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
Answer Posted / 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 |
Post New Answer View All Answers
How will you solve the problem that occurs during update?
There is a column with date in it. If I want to get just month how it can be done? Can I use sub string?
Can we collect statistics on multiple columns?
How is MLOAD Client System restarted after execution?
What are the steps to create a data model?
What is the opening step in basic teradata query script?
What is teradata? Also, list out some of the primary characteristics of teradata.
My table got locked during mload due to a failed job. What do I do to perform other operations on it?
Steps to create a data model?
What is the use of teradata system software?
What are the uses of client software involved in teradata?
What is the command in bteq to check for session settings ?
What is the purpose of joins in teradata?
What are the functions of a teradata dba?
How do you define Teradata?