Splitting Comma Seperated String to Rows
Posted: April 15, 2008 Filed under: sql, Uncategorized 10 CommentsGot the following email from one of my users:
I am trying to write a SQL select to get comma delimited values from one field in rows.
For example I have a Table T1
create table t1(a varchar2(30),
b varchar2(30));
insert into t1 values ('A','27.68%,2.78%,69.55%');
SELECT A, B FROM T1;
A B
===== =====
A 27.68%,2.78%,69.55%
Can you help me write a SQL which will give the following output?
A PERCENT
====== ========
A 27.68%
A 2.78%
A 69.55%
My solution is rather ugly, but it works (on 10g and above) and makes no assumptions about the number of elements in the list :
With t2 as (select A,','||B||',' as B from t1)
SELECT A,SUBSTR(B,INSTR(B,',',1,LEVEL)+1,INSTR(B,',',1,LEVEL+1)-INSTR(B,',',1,LEVEL)-1)
FROM t2
CONNECT BY LEVEL <= length(regexp_replace('[^\,]',''))-2 ;
Senior DBA had a simpler solution: “Just replace all the commas with new lines”.
I apologize in advance for using that old illegal “connect by” trick. Unsupported and all. Not recommended. Don’t try it on production.
You can remove the “connect by” if you join on (select rownum from dba_objects) or something similar.
create table t1(a varchar2(30), b varchar2(30));
insert into t1 values (‘A’,’27.68,2.78,69.55′);
insert into t1 values ('B','1,2,3');select a,column_value from t1,xmltable(b);
select a,xmlcast(column_value as number) b from t1,xmltable(b);
A B
--- ----------
A 27.68
A 2.78
A 69.55
A 27.68
A 2.78
A 69.55
B 1
B 2
B 3
MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM, I forget to write you need 11g
1) Bad design OR let the app do it.
2) This is even worse:
SQL> insert into t1 values (‘A2’, ‘12.4%,66%,99.1%,45.33%’);
SQL> commit;
Commit complete.
SQL> select a, regexp_substr(b, ‘[^%]+’, 1, ao.rn) from t1
join
(select rownum rn from all_objects where rownum < 100) ao
on
rn <= length(regexp_replace(b, ‘[^,]’,”))+1;
A REGEXP_SUBSTR(B,'[^%]+’,1,AO.RN)
—————————— ————————————————————————————————————————
A2 ,45.33
A2 ,99.1
A2 ,66
A2 12.4
A ,69.55
A ,2.78
A 27.68
7 rows selected.
Note: 100 could be the max number of commas.
great input. it not only solves the problem, but also gives the results quick. thanks so much for this post.
Laurent,
Cute XML solution! You forgot to mention that the % character is not supported.
Rudy,
The design is terrible, this goes without saying. Everyone knows this is bad design and yet I run into csv strings in different apps every few month.
I’m not sure that letting the app do this is an improvement.
Using regexp_substr is much clearer than substr-instr! Why didn’t I think of it?
Hello.
There is also another solution:
create or replace type myTableType as table
of varchar2 (255);
/
create or replace
function in_list( p_string in varchar2 ) return myTableType
as
l_string long default p_string || ‘,’;
l_data myTableType := myTableType();
n number;
begin
loop
exit when l_string is null;
n := instr( l_string, ‘,’ );
l_data.extend;
l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
l_string := substr( l_string, n+1 );
end loop;
return l_data;
end;
/
Then you can do the following:
SELECT * FROM TABLE(in_List(‘1,2,3,4,5’))
Now, related to the part of generating 100 rows, I also have an alternative solution:
SELECT ROWNUM FROM dual
CONNECT BY 1=1
AND ROWNUM < 100
I am not sure if these are very good for your case, but I just discovered your blog and wanted to leave a reply 🙂
Thanks, Radian.