Answer / tdguy

I hope substr function can be used but somewhat lengthy.
But UDF would make task easy.

SEL SIX
FROM
(
SEL
CASE
WHEN SUBSTR(COLUMN,1,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN '1' ELSE '' END AS ONE,
CASE
WHEN SUBSTR(COLUMN,2,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN '2' ELSE '' END AS TWO,
CASE
WHEN SUBSTR(COLUMN,3,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN '3' ELSE '' END AS THREE,
CASE
WHEN SUBSTR(COLUMN,4,1) IN
('1','2','3','4','5','6','7','8','9','0')
THEN '4' ELSE '' END AS FOUR,
SUBSTR(TRIM(ONE||TWO||THREE||FOUR),1,1) AS FIVE,
SUBSTR(COLUMN,FIVE,1) AS SIX
FROM TABLE
) A

Answer / rkraju

Hi,gautam nice 2 see ur answer can u show me that output in
one sql.,
thanks in advance

Answer / pavan

sel (case
when position('1' in name)>0 then substring(name from position('1' in name) for 1)
when position('2' in name)>0 then substring(name from position('2' in name) for 1)
when position('3' in name)>0 then substring(name from position('3' in name) for 1)
when position('4' in name)>0 then substring(name from position('4' in name) for 1)
when position('5' in name)>0 then substring(name from position('5' in name) for 1)
when position('6' in name)>0 then substring(name from position('6' in name) for 1)
when position('7' in name)>0 then substring(name from position('7' in name) for 1)
when position('8' in name)>0 then substring(name from position('8' in name) for 1)
when position('9' in name)>0 then substring(name from position('9' in name) for 1)
when position('0' in name)>0 then substring(name from position('0' in name) for 1)
else substring(name from 0 for 1)
end),name from ttemp.t2sel (case
when position('1' in name)>0 then substring(name from position('1' in name) for 1)
when position('2' in name)>0 then substring(name from position('2' in name) for 1)
when position('3' in name)>0 then substring(name from position('3' in name) for 1)
when position('4' in name)>0 then substring(name from position('4' in name) for 1)
when position('5' in name)>0 then substring(name from position('5' in name) for 1)
when position('6' in name)>0 then substring(name from position('6' in name) for 1)
when position('7' in name)>0 then substring(name from position('7' in name) for 1)
when position('8' in name)>0 then substring(name from position('8' in name) for 1)
when position('9' in name)>0 then substring(name from position('9' in name) for 1)
when position('0' in name)>0 then substring(name from position('0' in name) for 1)
else substring(name from 0 for 1)
end),name from ttemp.t2;

Answer / santosh

sel regexp_substr('aa6588fhfhf','[[:digit:]]+')
Ans:6588

Answer / ashutosh gautam

Use DIGITS("aa6588fhfhf") in transform it will return only
digit as 6588.

A Query was run fine earlier. It is not running properly now. what are proactive steps you can take as a dba ?

1 Answers  

What are the available join types in teradata?

0 Answers  

Difference between multiload and tpump?

0 Answers  

"pravalli nagireddy" record is there in address column but i want display only 'nagireddy' from the main string what is query for that pls tell me

9 Answers  

Does any body has TERADATA Certification Dumps, if any body is having please let me know to summee4you@gmail.o, it is very Very URGENT to me

25 Answers   TCS,

Can you load multiple data files for same target table using fastload?

1 Answers  

What is a pseudo key? What is its use???

2 Answers   Mphasis,

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?

9 Answers   IBM,

I Have Name field in a Table Name ------------- abcde;fgh;ijk; abc;def;jklm;no I want to write a sql to display name field data with out ';'.so i want to replace ; with '' how will write in Teradata

3 Answers   Wipro,

What is the default join strategy in Teradata???

5 Answers   IBM,

What is meant by a Parsing Engine?

0 Answers  

If the script is aborted. why it is aborted and how can you identify ?

1 Answers