Oracle- Split String Comma Delimited (String Contains Spaces and Consecutive Commas)

Oracle- Split string comma delimited (string contains spaces and consecutive commas)

Try this for the parsing the list part. It handles NULLS:

SQL> select regexp_substr('12 3,456,,abc,def', '(.*?)(,|$)', 1, level, null, 1) SPLIT, level
    from dual
    connect by level <= regexp_count('12 3,456,,abc,def',',') + 1
    ORDER BY level;

SPLIT                  LEVEL
----------------- ----------
12 3                       1
456                        2
                           3
abc                        4
def                        5

SQL>

Unfortunately when you search for regex's for parsing lists, you will always find this form which does NOT handle nulls and should be avoided: '[^,]+'. See here for more info: Split comma separated values to columns in Oracle.

How to split a string in order with a comma-separated string and (possible) empty fields between commas

with test as (select 'value1;value2;;;value5;value6;' line from dual)
select nvl(regexp_substr (line, '([^;]*);', 1, rownum, null, 1), 'none') as split
from test
connect by level <= regexp_count (line, ';');

SPLIT                         
------------------------------
value1
value2
none
none
value5
value6

6 rows selected. 

How to convert comma delimited values to records?

In Oracle, you can try this

with mydata as 
     (select q'[AX,BC]' mycol from dual)
       select regexp_substr(mycol, '[^,]+', 1, level) result from mydata
       connect by level <= length(regexp_replace(mycol, '[^,]+')) + 1; 

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.

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.

Replacing multiple commas with single comma in MS SQL

This is what i did.

select replace(replace(replace('a,,,b,,,c,d,e,,,,f',',','<>'),'><',''),'<>',',')

Convert comma separated string to array in PL/SQL

Oracle provides the builtin function DBMS_UTILITY.COMMA_TO_TABLE.

Unfortunately, this one doesn't work with numbers:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => l_input
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        l_array(i)
 18      );
 19    end loop;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6

But with a little trick to prefix the elements with an 'x', it works:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => regexp_replace(l_input,'(^|,)','\1x')
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        substr(l_array(i),2)
 18      );
 19    end loop;
 20  end;
 21  /
3
Element 1 of array contains: 1
Element 2 of array contains: 2
Element 3 of array contains: 3

PL/SQL procedure successfully completed.

Regards,
Rob.

Splitting every row of varchar column and create new row for every split part

Here's a full working example where the regex looks for a delimiter of a semi-colon followed by a space OR the end of the line:

SQL> WITH Tbl(WorkID, History) AS(
     select 1, 'Finished (30-05-2018);' from dual union all
     select 2, 'InProgress (25-05-2018); Rejected(26-05-2018); InProgress (28-05-2018); Finished (30-05-2018);' from dual union all
     select 3, 'InProgress (25-05-2018); Finished (30-05-2018);' from dual
   )
   select WorkID, regexp_substr(History, '(.*?)(; |;$)', 1, level, NULL, 1) history
   from Tbl
   connect by regexp_substr(History, '(.*?)(; |;$)', 1, level) is not null
   and prior WorkID = WorkID
   and prior sys_guid() is not null;

    WORKID HISTORY
---------- -------------------------
         1 Finished (30-05-2018)
         2 InProgress (25-05-2018)
         2 Rejected(26-05-2018)
         2 InProgress (28-05-2018)
         2 Finished (30-05-2018)
         3 InProgress (25-05-2018)
         3 Finished (30-05-2018)

7 rows selected.

---

Related Topics