Hi frnds...can any one help me regarding this que.. We have column in a table

Hi frnds...can any one help me regarding this que..

We have column in a table name it as C1 WHICH CONTAIN
ALPHANUMERIC AND NUMERIC VALUES.

C1
2A
2B
2V
2H
1
2
3
4
5

i want to retrive numeric values like 1,2,3,4,5

How we can get

Plz let me know.Thanks in advance

Question Posted / guest

4 Answers
7072 Views
IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

Hi frnds...can any one help me regarding this que.. We have column in a table name it as C1 WHICH..

Answer / santosh

Hi Friends some addition to 'Chelvam' ans.

SELECT C1 FROM <TABLE_NAME> WHERE UPPER(C1) =
C1(CASESPECIFIC) AND LOWER(C1) = C1(CASESPECIFIC)

Is This Answer Correct ?

5 Yes

0 No

Hi frnds...can any one help me regarding this que.. We have column in a table name it as C1 WHICH..

Answer / chelvam

select C1,
CASE WHEN ( C1( UPPERCASE) = C1(CS) ) THEN
'NUMERIC'
ELSE
'ALPHANUMERIC'
END as C11
from TableName;

Is This Answer Correct ?

3 Yes

2 No

Hi frnds...can any one help me regarding this que.. We have column in a table name it as C1 WHICH..

Answer / tdguy

Assuming the column to be char(2), we can use the following
sql to retrieve the numeric values.

SEL C1 FROM
(SEL C1,
CASE
WHEN SUBSTR(C1,1,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN 'N'
WHEN SUBSTR(C1,1,1) IN (' ')
THEN 'S'
END AS ONE,
CASE
WHEN SUBSTR(C1,2,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN 'N'
WHEN SUBSTR(C1,2,1) IN (' ')
THEN 'S'
END AS TWO FROM STUD2
) A
WHERE ((A.ONE='N' AND A.TWO='N')
OR
(A.ONE='N' AND A.TWO='S')
OR
(A.ONE='S' AND A.TWO='N'))

Is This Answer Correct ?

2 Yes

1 No

Hi frnds...can any one help me regarding this que.. We have column in a table name it as C1 WHICH..

Answer / parthi

SELECT * FROM C1
WHERE TD_SYSFNLIB.TO_NUMBER(C1) IS NOT NULL;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer