0

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
hotfix
  • 3,376
  • 20
  • 36
Oscar P
  • 61
  • 2
  • 12
  • Did you try [`[^;]*(?=;)`](https://regex101.com/r/YQTtNS/1)? – logi-kal Apr 04 '19 at 07:41
  • Hey, I tried your change, but the output is all "none"s. – Oscar P Apr 04 '19 at 07:47
  • Refer to this answer https://stackoverflow.com/a/35462641/7998591 – Kaushik Nayak Apr 04 '19 at 07:59
  • Solutions from the duplicates: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=81c82458f2e7d499d36a6ec513ae800a – MT0 Apr 04 '19 at 08:05
  • @horcrux Oracle does not support look-ahead/behind in regular expressions. – MT0 Apr 04 '19 at 08:06
  • @MT0 Duplicates are similar but does not include the required solution ( split a string in order with a comma-separated string with empty fields included, not removed ) – Oscar P Apr 04 '19 at 08:50
  • @OscarP Look at my DB<>Fiddle a couple of comments up. Those solutions are taken from the linked duplicates and do solve your question. You are correct that not all of the solutions in the duplicates do but there are solutions in the duplicates that solve your problem. I'll try and find more duplicates for you. – MT0 Apr 04 '19 at 08:54
  • @MT0 Ok, thank you! – Oscar P Apr 04 '19 at 08:55
  • .. and do look at my comments too, that's a solution for the question which is precisely the same problem, except you want to show nulls as none. [Here's](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a6a4429475a7964188ef1f546b2798ee) a dbfiddle of the same – Kaushik Nayak Apr 04 '19 at 09:01

1 Answers1

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.
Amir Kadyrov
  • 1,253
  • 4
  • 9