Tags

, , , , ,

NOTE : This post is about splitting a single comma delimited string. If you have to apply it on a table with multiple rows having comma delimited strings, then look at Split comma delimited strings in a table using Oracle SQL

This is one of the most common questions in most of the Oracle SQL and PL/SQL forums. Although, there are several examples and demo over the web, I thought to summarize all the approaches together at one place.

  1. Regular expressions
  2. XML table
  3. MODEL clause
  4. Table function
  5. Pipelined table function

Using REGULAR EXPRESSIONS

INSTR in CONNECT BY clause


SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_SUBSTR in CONNECT BY clause


SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5  FROM DATA
  6  CONNECT BY regexp_substr(str , '[^,]+', 1, LEVEL) IS NOT NULL
  7  /

STR
----------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

REGEXP_COUNT in CONNECT BY clause


SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4      SELECT trim(regexp_substr(str, '[^,]+', 1, LEVEL)) str
  5      FROM DATA
  6      CONNECT BY LEVEL <= regexp_count(str, ',')+1   
  7  / 
STR 
---------------------------------------- 
word1 
word2 
word3 
word4 
word5 
word6 

6 rows selected. 

SQL>

Using XMLTABLE


SQL> WITH DATA AS
  2    ( SELECT 'word1, word2, word3, word4, word5, word6' str FROM dual
  3    )
  4  SELECT trim(COLUMN_VALUE) str
  5    FROM DATA, xmltable(('"' || REPLACE(str, ',', '","') || '"'))
  6  /
STR
------------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using MODEL clause


SQL> WITH t AS
  2  (
  3    SELECT 'word1, word2, word3, word4, word5, word6' str
  4    FROM   dual ) ,
  5  model_param AS
  6  (
  7  SELECT str                                           orig_str ,
  8         ','
  9         || str
 10         || ','                                        mod_str,
 11         1                                             start_pos,
 12         Length(str)                                   end_pos,
 13         (Length(str) - Length(Replace(str, ','))) + 1 element_count,
 14         0                                             element_no,
 15         ROWNUM                                        rn
 16  FROM   t )
 17  SELECT   trim(Substr(mod_str, start_pos, end_pos-start_pos)) str
 18  FROM     (
 19  SELECT *
 20   FROM   model_param MODEL PARTITION BY (rn, orig_str, mod_str)
 21  DIMENSION BY (element_no)
 22  MEASURES (start_pos, end_pos, element_count)
 23  RULES ITERATE (2000)
 24  UNTIL (ITERATION_NUMBER+1 = element_count[0])
 25  (start_pos[ITERATION_NUMBER+1] = instr(cv(mod_str), ',', 1, 
 26                                   cv(element_no)) + 1,
 27   end_pos[iteration_number+1] = instr(cv(mod_str), ',', 1, 
 28                                 cv(element_no) + 1) ) )
 29  WHERE    element_no != 0
 30  ORDER BY mod_str ,
 31           element_no
 32  /

STR
------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using Table function

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

Type created.

SQL> CREATE OR REPLACE
  2    FUNCTION comma_to_table(
  3        p_list IN VARCHAR2)
  4      RETURN test_type
  5    AS
  6      l_string VARCHAR2(32767) := p_list || ',';
  7      l_comma_index PLS_INTEGER;
  8      l_index PLS_INTEGER := 1;
  9      l_tab test_type     := test_type();
 10    BEGIN
 11      LOOP
 12        l_comma_index := INSTR(l_string, ',', l_index);
 13        EXIT
 14      WHEN l_comma_index = 0;
 15        l_tab.EXTEND;
 16        l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string, 
 17                                          l_index, 
 18                                          l_comma_index - l_index
 19                                          )
 20                                   );
 21        l_index            := l_comma_index + 1;
 22      END LOOP;
 23      RETURN l_tab;
 24    END comma_to_table;
 25  /

Function created.

SQL> sho err
No errors.

SQL> SELECT * FROM 
  2  TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
  3  /

COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Using Pipelined Function

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

Type created.

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

Function created.

SQL> sho err
No errors.

SQL> SELECT * FROM   
  2  TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))
  3  /

COLUMN_VALUE
----------------------------------------------------------------------
word1
word2
word3
word4
word5
word6

6 rows selected.

SQL>

Hope it helps!