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

Question

I need to split a string in Oracle PL/SQL with comma-separated in order, including empty fields as "none" value. This is an example with 6 fields but can have more or less fields

line varchar2(100) := "value1;value2;;;value5;value6;";
WITH test AS (SELECT line FROM DUAL)
  SELECT NVL(REGEXP_SUBSTR (line, '[^;]+', 1, ROWNUM),'none') as SPLIT
    FROM test
  CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (line, '[^;]+'));

Output:

 value1      
 value2      
 value5      
 value6      
 none         
 none

Desirable output:

value1      
value2  
none      
none  
value5      
value6

@horcrux Oracle does not support look-ahead/behind in regular expressions. — MT0
– MT0, Commented Apr 4, 2019 at 8:06

Amir Kadyrov · Accepted Answer · 2019-04-04 07:50:12Z

3

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.

answered Apr 4, 2019 at 7:50

Amir Kadyrov

1,2886 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oscar P Over a year ago

Perfect answer. Thanks!!!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related