All Forums Analytics
chinnababu 18 posts Joined 02/07
07 Feb 2007
to Obtain the 5-character code (translated into upper-case) following the fourth comma

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 uppercasex1vqm-2nd row after 4th comma needs to translate to uppercaseVLHHV-3rd row after 4th comma needs to translate to uppercaseex rows in a colomnP09- 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,V LHHV,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 earlierChinna Babu

RGlass 35 posts Joined 09/04
08 Feb 2007

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

You must sign in to leave a comment.