|
|
|
Junior Member
 
Group: Forum Members
Last Login: 4/22/2007 2:58:00 PM
Posts: 18,
Visits: 1
|
|
I want to write a teradata SQL to retrieve the 5th digit value followed by 4th comma and needs to translate it to upper case
and if the string does not contain any comma OR less than 4 commas, then the query should come back with nulls.
see below wbp98 in 1st row after 4th comma and needs to translate to uppercase
x1vqm-2nd row after 4th comma needs to translate to uppercase
VLHHV-3rd row after 4th comma needs to translate to uppercase
ex rows in a colomn
P09-2001365000042108792,09262006,08022006,,wbp98,wbp98,,22,4,6,2,20,Landscaping,2,,1,2,,3,,5,2,,2,2,2,2,2,,-1,0,1,1,,0,0,2,,,2,,,2,559464948,545804394,,,,CM-N/C
P09-2005120107000029245,10262006,10262006,,x1vqm,f46cr,,3,12,6,2,20,Health,2,,1,2,,3,,7,2,,2,2,2,2,2,,-0.5,0,1,1,,0,0,2,,,2,2,2,2,132708155,,,,,CM-N/C
P19-2005181104000993310,01252007,11142006,91000088687315,VLHHV,W1KFN,,6,14,6,2,20,Non-Profit,2,,2,2,,10,,4,2,,2,2,2,3,2,,-1,0,1,1,,0,0,8,,,2,,,,,,,,,CM-N/C
Please help me in this issue
Thanks in advance who helped me earlier
Chinna Babu
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 4/17/2008 10:56:01 AM
Posts: 58,
Visits: 9
|
|
Chinna,
Something like this could be used in this case:
SELECT UPPER(PTL_STR) FROM
(SELECT SUBSTR(PTL_STR,POSITION(',' IN PTL_STR) +1,5) FROM
(SELECT SUBSTR(PTL_STR,POSITION(',' IN PTL_STR) +1, 50) FROM
(SELECT SUBSTR(PTL_STR, POSITION( ',' IN PTL_STR) +1 , 50) FROM
(select
substr('P09-2001365000042108792,09262006,08022006,,wbp98,wbp98,,22,4,6,2,20,
Landscaping,2,,1,2,,3,,5,2,,2,2,2,2,2,,-1,0,1,1,,0,0,2,,,2,,,2,559464948,545804394,,,,CM-N/C'
,POSITION( ',' IN
'P09-2001365000042108792,09262006,08022006,,wbp98,wbp98,,22,4,6,2,20
,Landscaping,2,,1,2,,3,,5,2,,2,2,2,2,2,,-1,0,1,1,,0,0,2,,,2,,,2,
559464948,545804394,,,,CM-N/C') +1 )) DT (PTL_STR))DT(PTL_STR)) DT(PTL_STR))DT(PTL_STR);
TBob
|
|
|
|