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.

What is FILLER command in Teradata?

9 Answers   CTS, IBM,

What are the joins in teradata and how many types of joins are there in teradata?

0 Answers  

What is dimensional modeling?

0 Answers  

What is the use of fallback?

0 Answers  

Hi All, I have a table with 3 fields like id,mark1,mark2 and I would like to update a mark3 field that would calculate the max for each record (so the max value of the 2 fields) in Teradata ID Mark1 Mark2 Mark3 1 10 20 2 20 30 3 40 10 4 50 50 I Have to write a update statement Mark3 with max value of mark1,mark2 fields…like bellow ID Mark1 Mark2 Mark3 1 10 20 20 2 20 30 30 3 40 10 40 4 50 50 50 Please any one help me ....Thq

3 Answers   Wipro,

Suppose i have data like 2009/12/31 555555534 ' ' Generally last 2 records are wrong format it is going to error table but i don't want load error table if any wrong format in Date column Pass "NULL" how can we do it fast load

4 Answers  

what is identity column in TD?

2 Answers   Wipro,

how teradata fetches a row from primary index table?

1 Answers  

which option is used to restart the fastexport script ?

2 Answers   TCS,

What is a sparse index?

0 Answers  

What are teradata utilities?

0 Answers  

List out teradata data types?

0 Answers