Teradata - need function a logic to identify patterns in a column

Teradata - need a string function/ logic to identify patterns in a column and turn the same into row as the pattern is identified.

A=ABC, B=AC, C=1234, D='Y' is a single column value.

Between = and , whatever value identified should go as column A and same for B, C, D columns.

Who is Participating?
flow01Connect With a Mentor Commented:
with inp
(select replace('A=ABC, B=AC, C=1234, D=''Y''','''','') src from dual) -- remove quotes if you want
select src,
replace(substr(src,1,strA -1),'A=',null) A,    -- use substr to get the part till first comma's
replace(substr(src,strA + 1,(strb-stra)-1),'B=',null) B, -- use substr to get the part between each pair of comma's
replace(substr(src,strB + 1,(strc-strb)-1),'C=',null) C, -- use substr to get the part between each pair of comma's
replace(substr(src,strc + 1),'D=',null) D -- use substr to get part after the 3e comma
-- use the replace to remove the X=
(select src,
instr(src,',',1,1) strA,  -- determine the delimiter positions
instr(src,',',1,2) strB,
instr(src,',',1,3) strC,
instr(src,',',1,4) srtD   -- i made the assumption srtD is always 0 : no ending comma
from inp
ManikandanNAuthor Commented:
I am not an DB expert but the requirment though was complex - solution for the same has been explained neatly with appropriate comments.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.