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  |
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 #358727 is a reply to message #358683] |
Wed, 12 November 2008 05:43   |
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 #359009 is a reply to message #358839] |
Thu, 13 November 2008 05:25  |
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.
|
|
|
Goto Forum:
Current Time: Sat Feb 15 23:15:24 CST 2025
|