Split Comma Separated Values of a Column in Row, Through Oracle SQL Query

Split comma separated values of a column in row, through Oracle SQL query

Try using below query:

 WITH T AS (SELECT 'A,B,C,D,E,F' STR  FROM DUAL)   SELECT    
 REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM T 
 CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM T)

Below Query with ID:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
)
SELECT    ID, 
REGEXP_SUBSTR (STR, '[^,]+', 1, LEVEL) SPLIT_VALUES  FROM TAB 
CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (STR, ',', NULL)) FROM TAB);

EDIT:
Try using below query for multiple IDs and multiple separation:

WITH TAB AS 
(SELECT '1001' ID, 'A,B,C,D,E,F' STR FROM DUAL
UNION
SELECT '1002' ID, 'D,E,F' STR FROM DUAL
UNION
SELECT '1003' ID, 'C,E,G' STR FROM DUAL
)
select id, substr(STR, instr(STR, ',', 1, lvl) + 1, instr(STR, ',', 1, lvl + 1) - instr(STR, ',', 1, lvl) - 1) name 
from
( select ',' || STR || ',' as STR, id from TAB ),
( select level as lvl from dual connect by level <= 100 )
where lvl <= length(STR) - length(replace(STR, ',')) - 1

order by ID, NAME

Separate comma separated string into columns oracle sql

You can use REGEXP_SUBSTR, but you must specify the number of columns.

SELECT agentname
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 1)    AS CATA
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 2)    AS CATB  
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 3)    AS CATC 
       ,REGEXP_SUBSTR (categories, '[^,]+', 1, 4)    AS CATD
FROM commasplit;

demo in db<>fiddle

Split comma separated values with line gaps and nulls into columns in table via pl/sql procedure

Your regular expression needs to allow for nulls, i.e. consecutive commas (but hopefully you don't have commas within any of the quoted strings...). If you have multiple source rows then it's easier to split with a recursive CTE:

with rcte (id, data, lvl, result) as (
  select id, data, 1, regexp_substr(data, '(.*?)(,|$)', 1, 1, null, 1)
  from disp_data
  union all
  select id, data, lvl + 1, regexp_substr(data, '(.*?)(,|$)', 1, lvl + 1, null, 1)
  from rcte
  where lvl <= regexp_count(data, ',')
)
select id, lvl, result
from rcte
order by id, lvl;

You can then pivot the result into the columns you want:

with rcte (id, data, lvl, result) as (
  select id, data, 1, regexp_substr(data, '(.*?)(,|$)', 1, 1, null, 1)
  from disp_data
  union all
  select id, data, lvl + 1, regexp_substr(data, '(.*?)(,|$)', 1, lvl + 1, null, 1)
  from rcte
  where lvl <= regexp_count(data, ',')
)
select *
from (
  select id, lvl, result
  from rcte
)
pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));

db<>fiddle

And you can use that directly in an insert statement:

insert into push_data_temp (pid,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11)
with rcte (id, data, lvl, result) as (
  select id, data, 1, regexp_substr(data, '(.*?)(,|$)', 1, 1, null, 1)
  from disp_data
  union all
  select id, data, lvl + 1, regexp_substr(data, '(.*?)(,|$)', 1, lvl + 1, null, 1)
  from rcte
  where lvl <= regexp_count(data, ',')
)
select *
from (
  select id, lvl, result
  from rcte
)
pivot (max(result) as col for (lvl) in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11));

db<>fiddle

No PL/SQL needed, but you can still wrap it in a procedure if you want to.

---

I have to take as clob and it is throwing error as inconsistent datatype

You need to cast the tokens as varchar2, which limits their length (either 4k or 32k depending on Oracle version and settings):

with rcte (id, data, lvl, result) as (
  select id, data, 1,
    cast(regexp_substr(data, '(.*?)(,|$)', 1, 1, null, 1) as varchar2(4000))
  from disp_data
  union all
  select id, data, lvl + 1,
    cast(regexp_substr(data, '(.*?)(,|$)', 1, lvl + 1, null, 1) as varchar2(4000))
  from rcte
  where lvl <= regexp_count(data, ',')
)
...

db<>fiddle with CLOB (and connect-by examples removed, as they break it...)

---

when i try for text with commas in between, it splits data unevenly.

That's why I said "hopefully you don't have commas within any of the quoted strings". As you don't have any really empty elements - you have ...","","... rather than ...,,... - you can skip the concern about those I suppose, and use a different pattern:

with rcte (id, data, lvl, result) as (
  select id, data, 1,
    cast(regexp_substr(data, '("[^"]*"|[^,]+)', 1, 1, null, 1) as varchar2(4000))
  from disp_data
  union all
  select id, data, lvl + 1,
    cast(regexp_substr(data, '("[^"]*"|[^,]+)', 1, lvl + 1, null, 1) as varchar2(4000))
  from rcte
  where lvl <= regexp_count(data, '("[^"]*"|[^,]+)')
)
...

db<>fiddle

If you did have to deal with null elements then it's still possible, but more work. This also won't deal with escaped double-quotes without strings. At some point it will be easier to write your own parser in PL/SQL; or even to write the data to disk and read it back in as an external table which can handle all of this for you.

Split comma separated values to columns in Oracle

You can use regexp_substr():

select regexp_substr(val, '[^,]+', 1, 1) as val1, 
       regexp_substr(val, '[^,]+', 1, 2) as val2, 
       regexp_substr(val, '[^,]+', 1, 3) as val3, 
       . . .

I would suggest that you generate a column of 255 numbers in Excel (or another spreadsheet), and use the spreadsheet to generate the SQL code.

---

Related Topics