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

Posted on 2009-12-23
Medium Priority
Last Modified: 2012-06-21
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.

Question by:ManikandanN
LVL 21

Accepted Solution

flow01 earned 1000 total points
ID: 26114625
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

Author Closing Comment

ID: 31669515
I am not an DB expert but the requirment though was complex - solution for the same has been explained neatly with appropriate comments.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question