Link to home
Start Free TrialLog in
Avatar of toooki
toooki

asked on

Parsing string in Oracle

I have a Oracle 11gR2 database table MyTab1 and another table MyTab2:

How can I write a query that will parse the F2 field (field contents are comma separated) of MyTab1 and join its value with the F2 field of MyTab2 to provide O/P like:

O/P:
F2    
---------------------
abc: 200, pqr: 211, mn: 13, rt:121
ct: 333, fg: 244, sdg: 333
mx: 21
......
......
......

The O/P will have the same number of records as in MyTab1. I need to write a efficient query because the tables are large... Thanks for help in advance.


MyTab1:
--------
F2
--------
abc, pqr, mn, rt
ct, fg, sdg
mx
tr,  md, gh
df,
fg, df

MyTab2:
--------
F2       F3
-------------
abc     200
pqr      211
mn      13
mx       21
mg      675
rt         121
df      233
ct 333
fg   244
sdg  333

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
select wm_concat(t2.f2 || ':' || t2.f3) from myTab1,myTab2 t2
where instr(t1.f2,t2.f2)>0
group by t1.f2

I missed an alias in previous post . .here is the updated one..

select wm_concat(t2.f2 || ':' || t2.f3) from myTab1 t1,myTab2 t2
where instr(t1.f2,t2.f2)>0
group by t1.f2
Avatar of Sean Stuber
Sean Stuber

wm_concat is not a supported function  in any version, yes, it exists in some versions but has never been recommended or supported by oracle


in 11gR2   listagg is supported.

also note,  the instr method isn't reliable

for instance,  if MyTab2  has an element "d"  that will give a false positive on df, md, and sdg
Avatar of toooki

ASKER

Thank you very much.

I am going to try listagg and will check wm_concat.

sdstuber. if possible, could help me understand:
(SELECT f2 z, ROWNUM rn, TRIM(COLUMN_VALUE) x
          FROM mytab1, TABLE(str2tbl(f2)))

What the above query is doing..?  The keywords  COLUMN_VALUE and TABLE -- I couldn't understand.


the table function turns the result set of str2tbl into a virtual table


so  'a,b,c'  becomes a "table" of 3 rows

'a'
'b'
'c'

str2tbl will generate the data from the strings in the same order they were in the original string
since the data itself doesn't have any sorting criteria other than position,  using rownum gives me a number I can use reliably to sort


easiest way to understand it is to run just that portion and look at the results

Avatar of toooki

ASKER

Thanks a lot for explaining!!

I am going to try the query and will check the result.
Avatar of toooki

ASKER

sdstuber, everything worked more than perfectly.
I used all that you mentioned above. And everything worked just the way I wanted.
Many thanks.