Skip to Main Content
  • Questions
  • Convert comma separated values in a column into rows and Join the result set with another table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Raghu.

Asked: August 27, 2018 - 9:56 am UTC

Last updated: September 12, 2018 - 3:19 am UTC

Version: 18.1.0.095.1630

Viewed 50K+ times! This question is

You Asked

I have a table as below

R_ID R_Site R_Name R_Role
----- ------------- ------ ------
1 123,-456,-789 qwer Owner
2 56,-741-852 qaz Manager
3 369,-741,-987 wsx Employee
4 All eddc Employee
5 All rfv Employee

And used the below query to convert the R_Site Comma separated values into rows

select REGEXP_SUBSTR (txt, '[^,]+', 1, level) as SITE , R_NAME
from (select distinct R_SITE as txt, R_NAME from MY_TABLE
connect by level <= length(regexp_replace(txt,'[^,]*'))+1

Now I need to join the above result set to the table like below one on R_SITE(SITE) = S_SITE to get S_Sal value in the final result

S_ID S_Site S_Sal S_Mgr
----- ------ ----- -----
1 -789 10000 Raghu
2 -123 20000 Mahesh
3 -456 300000 Swetha
4 -147 400000 Vamsi
5 All 500000 Narendra

But when I join, the db has been spinning for ever and not giving any result.

Could you please let me know if I'm doing wrong in any step and ways to correct it.

SQL Updates:
---------
Source table
CREATE TABLE MY_TABLE(
R_ID int,
R_Site varchar(255),
R_Name varchar(255),
R_Role varchar(255)
);

INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');

INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');

I would like to have the result like the below table after splitting the comma separated string to rows

CREATE TABLE MY_TABLE(
R_ID int,
R_Site varchar(255),
R_Name varchar(255),
R_Role varchar(255)
);

INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '123', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '-456,', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('1' , '-789', 'qwer' , 'Owner');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '56', 'qaz' , 'Manager');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '-741', 'qaz' , 'Manager');
INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
VALUES ('2' , '-852', 'qaz' , 'Manager');

I am able to achieve this effectively by using regular exp and connect by level. But I would like to have a solution in ASCI standard.

Thanks in advance.





and Connor said...

Thanks for putting it into SQL so we can work with it.

Even though you put "18" for the version, I'm going to assume thats a SQL Developer version, so here is something that will work in 11g. A spinning query often results if your calculation of the number of commas is wrong, so you end up "connect by"-ing forever. We can use cast/multiset to get the number of commas *per row* to avoid this

SQL> CREATE TABLE MY_TABLE(
  2  R_ID int,
  3  R_Site varchar(20),
  4  R_Name varchar(20),
  5  R_Role varchar(20)
  6  );

Table created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('1' , '123,-456,-789', 'qwer' , 'Owner');

1 row created.

SQL>
SQL> INSERT INTO MY_TABLE(R_ID , R_Site , R_Name , R_Role )
  2  VALUES ('2' , '56,-741,-852', 'qaz' , 'Manager');

1 row created.

SQL>
SQL>
SQL> select
  2    t.r_id,
  3    t.r_name,
  4    t.r_role,
  5    regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value)  as site
  6  from
  7    MY_TABLE t,
  8    table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.R_Site, '[^,]+'))  + 1) as sys.OdciNumberList)) commas
  9  order by 1,2,3,4;

      R_ID R_NAME                                                       R_ROLE                                                       SITE
---------- ------------------------------------------------------------ ------------------------------------------------------------ --------------------
         1 qwer                                                         Owner                                                        -456
         1 qwer                                                         Owner                                                        -789
         1 qwer                                                         Owner                                                        123
         2 qaz                                                          Manager                                                      -741
         2 qaz                                                          Manager                                                      -852
         2 qaz                                                          Manager                                                      56

6 rows selected.


From that point, that result set is just like any other table, so if you want to join with something else, a WITH clause keeps it simple, ie

with my_Results as 
(
select
  t.r_id,
  t.r_name,
  t.r_role,
  regexp_substr(t.R_Site, '[^,]+', 1, commas.column_value)  as site
from
  MY_TABLE t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.R_Site, '[^,]+'))  + 1) as sys.OdciNumberList)) commas
)
select ...
from   my_results,
       [other table]
where ...



I'm not sure what you mean by not ANSI standard - if by that you mean you do not want to use CONNECT BY then there are other ways. Here's a nice summary:

https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another, simply way

Paul, January 17, 2021 - 11:44 pm UTC

Hi.
I made a simply solution. Even to use in simply SQL query, without sophisticated functions:


select regexp_substr('&variable', '[^,]+', 1, comma.column_value)  as variable
                   from table(cast(multiset(select level from dual connect by level <= length (regexp_replace('&variable', '[^,]+'))  + 1) as sys.OdciNumberList)) comma


If you enter as
 &variable
value: abc,bcd,cde,def

you will get in return:

1 abc
2 bcd
3 cde
4 def

Now it's simple to use in IN clause.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library