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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
select wm_concat(t2.f2 || ':' || t2.f3) from myTab1 t1,myTab2 t2
where instr(t1.f2,t2.f2)>0
group by t1.f2
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
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
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.
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
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
ASKER
Thanks a lot for explaining!!
I am going to try the query and will check the result.
I am going to try the query and will check the result.
ASKER
sdstuber, everything worked more than perfectly.
I used all that you mentioned above. And everything worked just the way I wanted.
Many thanks.
I used all that you mentioned above. And everything worked just the way I wanted.
Many thanks.
where instr(t1.f2,t2.f2)>0
group by t1.f2