• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 496
  • Last Modified:

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.
0
toooki
Asked:
toooki
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
johanntagleCommented:
You will need to play around with the instr and substr functions

name should be:  substr(myval,1,instr(myval,',',1,1)-1);
ph should be:  SUBSTR(myval, INSTR(myval,',', 1, 1)+1,INSTR(myval,',',1,2)-INSTR(myval,',',1,1)-1)
dob should be: SUBSTR(myval, INSTR(myval,',', -1, 1)+1)
0
 
johanntagleCommented:
Oops submitted too soon.  To complete the query:

select id, 
        substr(myfunction(id),1,instr(myfunction(id),',',1,1)-1) as name,
        SUBSTR(myfunction(id), INSTR(myfunction(id),',', 1, 1)+1,INSTR(myfunction(id),',',1,2)-INSTR(myfunction(id),',',1,1)-1) as ph,
        SUBSTR(myfunction(id), INSTR(myfunction(id),',', -1, 1)+1) as dob
from MYTAB;

Open in new window


OR better if:

select id, substr(myval,1,instr(myval,',',1,1)-1) as name,
        SUBSTR(myval, INSTR(myval,',', 1, 1)+1,INSTR(myval,',',1,2)-INSTR(myval,',',1,1)-1) as ph,
        SUBSTR(myval, INSTR(myval,',', -1, 1)+1) as dob
from
(SELECT id, myfunction(id) as myval from MYTAB );

Open in new window


So it's not too cluttered and you only have to evaluate myfunction(id) once per id
0
 
slightwv (䄆 Netminder) Commented:
Here is what I have.

Tested using sqlplus and 10.2.0.1.

apologize if it is a repeat of previous posts.
drop table tab1 purge;
create table tab1(id number, myval varchar2(50));
insert into tab1 values(10,'name1,ph1,dob1');
insert into tab1 values(20,'name2,ph2,dob2');
insert into tab1 values(30,'name3,ph3,dob3');
insert into tab1 values(40,'a,b,c');
commit;
col name form a10
col ph form a10
col dob form a10


select id,
	substr(myval,1,instr(myval,',',1,1)-1) as name,
	substr(myval,
		instr(myval,',',1,1)+1,
		instr(myval,',',1,2)-instr(myval,',',1,1)-1
	) as ph,
	substr(myval,
		instr(myval,',',1,2)+1
	) as dob
from tab1;

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
slightwv (䄆 Netminder) Commented:
just tested the first query in http:#a35451524 against my test case and it is correct.

Sorry for a duplicate post.
0
 
toookiAuthor Commented:
Thanks a lot.
II am testing the queries you sent to me. Many thanks.
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
johanntagleCommented:
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.
0
 
sdstuberCommented:
for 11g,  regular expressions are much simpler


select id,
       regexp_substr(myval,'[^,]+',1,1) name,
       regexp_substr(myval,'[^,]+',1,2) ph,
       regexp_substr(myval,'[^,]+',1,3)dob
from tab1


this would work in 10g or higher too
0
 
johanntagleCommented:
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!
0
 
toookiAuthor Commented:
Many thanks!

The query:
select id,
        substr(myfunction(id),1,instr(myfunction(id),',',1,1)-1) as name,
        SUBSTR(myfunction(id), INSTR(myfunction(id),',', 1, 1)+1,INSTR(myfunction(id),',',1,2)-INSTR(myfunction(id),',',1,1)-1) as ph,
        SUBSTR(myfunction(id), INSTR(myfunction(id),',', -1, 1)+1) as dob
from MYTAB;

AND

select id,
       regexp_substr(myval,'[^,]+',1,1) name,
       regexp_substr(myval,'[^,]+',1,2) ph,
       regexp_substr(myval,'[^,]+',1,3)dob
from tab1

Both the above worked perfectly. Thank you.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now