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

Question

I can't find a solution about how to split a comma-delimited string in ORACLE. Searched a lot, nothing works for my case

Code

DECLARE
  TYPE T_ARRAY_OF_VARCHAR IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
  MY_ARRAY T_ARRAY_OF_VARCHAR;
  MY_STRING VARCHAR2(2000) := '12 3,456,,abc,def';
BEGIN
 FOR CURRENT_ROW IN (
    with test as    
      (select MY_STRING from dual)
      select regexp_substr(MY_STRING, '[^,]+', 1, rownum) SPLIT
      from test
      connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1)

  LOOP
   DBMS_OUTPUT.PUT_LINE('>' || CURRENT_ROW.SPLIT || '<');
   --DBMS_OUTPUT.PUT_LINE(CURRENT_ROW.SPLIT);
    MY_ARRAY(MY_ARRAY.COUNT) := CURRENT_ROW.SPLIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Array Size:' || MY_ARRAY.COUNT);
END;

/

The output is:

>12 3<
>456<
>abc<
>def<
><
Array Size:5

The empty value is out of order!!!!

check this: http://www.oracle-developer.net/display.php?id=412 — MaxU - stand with Ukraine, Feb 16 '16 at 22:38
and this: http://stackoverflow.com/questions/3142665/how-to-convert-csv-to-table-in-oracle — MaxU - stand with Ukraine, Feb 16 '16 at 22:40
I want the null result in the result set in the right order. The relevance of the 'space' in the title is that many solutions which I found do not work if the space is in the string to split (I. e. http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql) — Alex, Feb 16 '16 at 23:11
I can write this in a single query without any user defined function created. Would you like to have the answer? — Dinesh V, Feb 17 '16 at 00:58

score 9 · Accepted Answer · answered Feb 17 '16 at 16:42

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.

score 3 · Answer 2 · answered Feb 17 '16 at 08:00

3

Try xmltable and flwor expresion. The following example is not secure and throw error if you put string without comma. But is simpler to understand.

select xmlcast(column_value as varchar2(2000))  value_list 
  from xmltable('for $val in ora:tokenize($strList,",") 
                  return $val' 
                 passing '12 3,456,,abc,def' as "strList"
                );

And secured version.

    select xmlcast(column_value as varchar2(2000)) value_list 
      from xmltable('for $val at $index in ora:tokenize(concat(",",$strList),",") 
       where $index > 1 
       return $val' passing '12 3,456,,abc,def' as "strList"
               );

answered Feb 17 '16 at 08:00

Arkadiusz Łukasiewicz

6,241
1
11
17

Or, you can prevent that `ORA-19176 FORX0003` missing delimiter error by changing `ora:tokenize` to `fn:tokenize` – Amit Naidu Nov 01 '18 at 19:44
The first xmltable expression `'for $val in ora:tokenize($strList,",") return $val'` can be simplified to just `ora:tokenize($strList, ",")`. The second FLWOR expression can be done more simply as `if (contains($strList, ",")) then ora:tokenize($strList,"\,") else $strList` – Amit Naidu Nov 01 '18 at 19:59

Piotr Siekierski · Answer 3 · 2016-02-17T00:45:56.690

Little modification to your query, assuming you can pick one char which will not be present in MY_STRING, e.g. pipe |

   with test as    
  (select '12 3,456,,,,abc,def' MY_STRING from dual)
  select trim('|' from regexp_substr(regexp_replace(MY_STRING,',,',',|,|'),'[^,]+',1,level)) SPLIT
  from test
  connect by level <= length (regexp_replace(MY_STRING, '[^,]+'))  + 1;

Output:

 SPLIT                 
-----------------------
12 3                    
456                     
(null)                        
(null)                         
(null)                         
abc                     
def

noooo. What if I have three commas? This does not work! – Alex Feb 17 '16 at 00:02 — Alex, Feb 17 '16 at 00:02

Lalit Kumar B · Answer 4 · 2016-02-17T07:00:39.900

No need of PL/SQL, you could do it in plain SQL. See Split comma delimited strings in a table in Oracle.

Using MODEL clause:

WITH sample_data AS (
SELECT '12 3,456,,,,,abc,def' str FROM dual
)
-- end of sample_data mimicking real table
,
model_param AS (
SELECT str AS orig_str ,
       ','
       || str
       || ','                                 AS mod_str ,
       1                                      AS start_pos ,
       Length(str)                           AS end_pos ,
       (LENGTH(str) -
       LENGTH(REPLACE(str, ','))) + 1        AS element_count ,
       0                                      AS element_no ,
       ROWNUM                                 AS rn
       FROM   sample_data )
       SELECT trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
       FROM (
             SELECT *
             FROM   model_param
             MODEL PARTITION BY ( rn, orig_str, mod_str)
             DIMENSION BY (element_no)
             MEASURES (start_pos, end_pos, element_count)
             RULES ITERATE (2000)
             UNTIL (ITERATION_NUMBER+1 = element_count[0])
           ( start_pos[ITERATION_NUMBER+1] =
                     instr(cv(mod_str), ',', 1, cv(element_no)) + 1,
             end_pos[ITERATION_NUMBER+1] =
                     instr(cv(mod_str), ',', 1, cv(element_no) + 1) )
           )
        WHERE    element_no != 0
   ORDER BY      mod_str ,
                 element_no
   /

Output

STR
----------------------
12 3
456




abc
def

8 rows selected.

If you want to do it in PL/SQL, then you could use a pipelined table function:

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

SQL> CREATE OR REPLACE FUNCTION comma_to_table(
  2      p_list IN VARCHAR2)
  3    RETURN test_type PIPELINED
  4  AS
  5    l_string LONG := p_list || ',';
  6    l_comma_index PLS_INTEGER;
  7    l_index PLS_INTEGER := 1;
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT
 12    WHEN l_comma_index = 0;
 13      PIPE ROW ( TRIM(SUBSTR(l_string, l_index, l_comma_index - l_index)));
 14      l_index := l_comma_index                                + 1;
 15    END LOOP;
 16  RETURN;
 17  END comma_to_table;
 18  /

Function created.

Let's see the output:

SQL> SELECT *
  2  FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))
  3  /

COLUMN_VALUE
------------------------------------------------------------------------------
12 3
456




abc
def

8 rows selected.

SQL>

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

4 Answers4

Linked

Related