Home » SQL & PL/SQL » SQL & PL/SQL » Extracting a substring from delimited string (Oracle 10g)
Extracting a substring from delimited string [message #358683] Wed, 12 November 2008 02:48 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I have a task of extracting a substring from a delimited string with delimiter as ':'

In one table, I have two columns one with primary key field and second column is a description field that contains the delimited string having 4 values. ex: <1>:<2>:<3>:<4>.

By using this delimiter string, I have to extract all the 4 fields and store in 4 columns of another table for the same primary key.

Here is my test case:

--Creating reference table. a -> primary key ; b -> delimited string
create table pras_refData(a number(10), b varchar2(40));


--Creating distribution table. a -> primary key ; col[1-4] -> columns in which I have to store the extracted 4 fields. 
create table pras_distData(a number(10),col1 varchar2(20),col2 varchar2(20),col3 varchar2(20),col4 varchar2(20));


--Inserting test data
insert into pras_refData 
select 1,'a:bcd:efg:hij' from dual union
select 2,'klm:nop:qrs:' from dual union
select 3,':tuv::wxyz' from dual;

insert into pras_distData 
select 1,null,null,null,null from dual union
select 2,null,null,null,null from dual union
select 3,null,null,null,null from dual;


I can able to achieve the desired result using following sql query:

update pras_distData t2 
set    col1=(select substr(b,1,instr(b,':',1)-1)
             from pras_refData t1 where t1.a=t2.a),
       col2=(select substr(b,instr(b,':',1,1)+1,instr(b,':',1,2)-instr(b,':',1,1)-1)
             from pras_refData t1 where t1.a=t2.a),
       col3=(select substr(b,instr(b,':',1,2)+1,instr(b,':',1,3)-instr(b,':',1,2)-1)
             from pras_refData t1 where t1.a=t2.a),
       col4=(select substr(b,instr(b,':',1,3)+1)
             from pras_refData t1 where t1.a=t2.a);


SQL>  select * from pras_distData;

          A COL1                 COL2                 COL3                 COL4
----------- -------------------- -------------------- -------------------- --------------------
          1 a                    bcd                  efg                  hij
          2 klm                  nop                  qrs                  
          3                      tuv                                       wxyz


But here I used substr function with 2 to 3 internal instr functions. Just curious to know is there any better way of doing this? Or is Oracle providing any builtin functions for this?

Thanks in advance.

prashas_d.
Re: Extracting a substring from delimited string [message #358691 is a reply to message #358683] Wed, 12 November 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No there is none.

Regards
Michel
Re: Extracting a substring from delimited string [message #358727 is a reply to message #358683] Wed, 12 November 2008 05:43 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
But this query could be improved as follows.
Query1
update pras_distData t2 
set    col1=(select substr(b,1,instr(b,':',1)-1)
             from pras_refData t1 where t1.a=t2.a),
       col2=(select substr(b,instr(b,':',1,1)+1,instr(b,':',1,2)-instr(b,':',1,1)-1)
             from pras_refData t1 where t1.a=t2.a),
       col3=(select substr(b,instr(b,':',1,2)+1,instr(b,':',1,3)-instr(b,':',1,2)-1)
             from pras_refData t1 where t1.a=t2.a),
       col4=(select substr(b,instr(b,':',1,3)+1)
             from pras_refData t1 where t1.a=t2.a);

Query2
update pras_distData t2                    
set (col1,col2,col3,col4) =(select substr(b,1,instr(b,':',1)-1),
                                   substr(b,instr(b,':',1,1)+1,instr(b,':',1,2)-instr(b,':',1,1)-1),
                                   substr(b,instr(b,':',1,2)+1,instr(b,':',1,3)-instr(b,':',1,2)-1),
                                   substr(b,instr(b,':',1,3)+1)
             from pras_refData t1 where t1.a=t2.a);

Compare the explain plan between these two queries. You will find the first query will access the pras_refdata four times whereas the second one accesses it only once. Having said that optimiser is very clever it may decide to do the same (accessiing the table only once because the filter criteria is the same) when the query gets executed. For your original question you have already got an answer.

Regards

Raj

[Updated on: Wed, 12 November 2008 05:44]

Report message to a moderator

Re: Extracting a substring from delimited string [message #358839 is a reply to message #358727] Wed, 12 November 2008 11:06 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member

Thanks Raj !!

Though its a very basic, i didn't know that we can use set command together for 2 or more columns in ().

Thanks for letting me know.
Re: Extracting a substring from delimited string [message #359009 is a reply to message #358839] Thu, 13 November 2008 05:25 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As you're on 10g, you can use regular expressions to extract fields from a delimited list:
select a,lvl,replace(regexp_substr(b,'(:[^:]*)|(^[^:]*)',1,lvl),':',''),b
from   pras_refdata
     ,(select level lvl from dual connect by level <= 100)
order by a,lvl;


The trick is this bit:
regexp_substr(b,'(:[^:]*)|(^[^:]*)',1,lvl)

This looks for each set of characters that matches either:
1) Start of the line followed by 0 or more non-comma chrs
2) comma followed by zero or more non-comma chrs.

The lvl parameter lets you look for the n-th string that matches this pattern.
Previous Topic: Output of two queries in single row having complex relationship
Next Topic: Problem with REF cursor
Goto Forum:
  


Current Time: Sat Feb 15 23:15:24 CST 2025