toooki
asked on
Parsing Oracle varcha2 string
I have a question about parsing a Oracle varchar2 string in Oralce 11gR2:
SELECT id, myfunction(id) as myval from MYTAB;
The O/P of the above query will be like:
id myval
=================
10 name1,ph1,dob1
20 name2,ph2,dob2
30 name3,ph3,dob3
.....
How can I write a query on top of the above query to get an Output like:
id name ph dob
=================
10 name1 ph1 dob1
20 name2 ph2 dob2
30 name3 ph3 dob3
......
i.e. parsing the myval field content (comma separated) and putting the delimited values in columns (I know there will be three values (2-comma separation) in the myval field.
SELECT id, myfunction(id) as myval from MYTAB;
The O/P of the above query will be like:
id myval
=================
10 name1,ph1,dob1
20 name2,ph2,dob2
30 name3,ph3,dob3
.....
How can I write a query on top of the above query to get an Output like:
id name ph dob
=================
10 name1 ph1 dob1
20 name2 ph2 dob2
30 name3 ph3 dob3
......
i.e. parsing the myval field content (comma separated) and putting the delimited values in columns (I know there will be three values (2-comma separation) in the myval field.
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.
just tested the first query in http:#a35451524 against my test case and it is correct.
Sorry for a duplicate post.
Sorry for a duplicate post.
ASKER
Thanks a lot.
II am testing the queries you sent to me. Many thanks.
II am testing the queries you sent to me. Many thanks.
I should also add that a common problem with queries like this is a comma actually being part of the data.
if a comma can be part of the data, the function generating the CSV and your code will need to account for it.
if a comma can be part of the data, the function generating the CSV and your code will need to account for it.
if a comma can be part of the data, the function generating the CSV and your code will need to account for it.
Good call slightwv! And I like the way you formatted your query - I never thought of splitting sub-function, so my very complex queries tend to look cluttered. I will adopt that formatting from now on.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ooh nice. Thanks sdstuber. The substr-instr combination has been in my code library for so long I never thought of changing it. But now I will!
ASKER
Many thanks!
The query:
select id,
substr(myfunction(id),1,in
SUBSTR(myfunction(id), INSTR(myfunction(id),',', 1, 1)+1,INSTR(myfunction(id),
SUBSTR(myfunction(id), INSTR(myfunction(id),',', -1, 1)+1) as dob
from MYTAB;
AND
select id,
regexp_substr(myval,'[^,]+
regexp_substr(myval,'[^,]+
regexp_substr(myval,'[^,]+
from tab1
Both the above worked perfectly. Thank you.
The query:
select id,
substr(myfunction(id),1,in
SUBSTR(myfunction(id), INSTR(myfunction(id),',', 1, 1)+1,INSTR(myfunction(id),
SUBSTR(myfunction(id), INSTR(myfunction(id),',', -1, 1)+1) as dob
from MYTAB;
AND
select id,
regexp_substr(myval,'[^,]+
regexp_substr(myval,'[^,]+
regexp_substr(myval,'[^,]+
from tab1
Both the above worked perfectly. Thank you.
name should be: substr(myval,1,instr(myval
ph should be: SUBSTR(myval, INSTR(myval,',', 1, 1)+1,INSTR(myval,',',1,2)-
dob should be: SUBSTR(myval, INSTR(myval,',', -1, 1)+1)