to Obtain the 5-character code (translated into upper-case) following the fourth comma

Analytics
Enthusiast

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 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
1 REPLY
Enthusiast

Re: to Obtain the 5-character code (translated into upper-case) following the fourth comma

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