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

TBob — Thu, 08 Feb 2007 14:31:41 GMT

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

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

chinnababu — Wed, 07 Feb 2007 12:40:58 GMT

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

Teradata Forums / Teradata / Teradata Advanced Analytics User Group / to Obtain the 5-character code (translated into upper-case) following the fourth comma / Latest Posts

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

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