String split methods

This article show how to translate string separated by a character

'STR1,STR2,STR3,STR4'

into rows

STR_REC               
-------------------
STR1
STR2
STR3
STR4

CONNECT BY

This method uses regular expressions and CONNECT BY. Nice but can be hard for read and use

SELECT regexp_substr('STR1,STR2,STR3,STR4','[^,]+', 1, level) str_rec
  FROM dual
CONNECT BY LEVEL <= (1+regexp_count('STR1,STR2,STR3,STR4', ',')); 
STR_REC               
-------------------
STR1               
STR2               
STR3               
STR4

COLLECTION

Almost the same as above but I have wrapped it in function to simplify sql statements

CREATE OR REPLACE TYPE t_agg_rec AS OBJECT
(
  str_rec CLOB
)
/

CREATE OR REPLACE TYPE t_agg_tbl FORCE AS TABLE OF t_agg_rec;
/

CREATE OR REPLACE FUNCTION FN_SPLIT_CONVERTED
(
  in_str        IN CLOB,
  in_del        IN VARCHAR2
)
RETURN t_agg_tbl PIPELINED 
AS 
BEGIN
  FOR i IN (
    SELECT regexp_substr(in_str,'[^'||in_del||']+', 1, level) str_rec
      FROM dual
    CONNECT BY LEVEL <= (1+regexp_count(in_str,in_del)))
  LOOP
    PIPE ROW(t_agg_rec(i.str_rec));
  END LOOP;
  
  RETURN;
END FN_SPLIT_CONVERTED;

SELECT * 
  FROM 
TABLE(fn_split_converted('STR1,STR2,STR3,STR4', ','));

STR_REC   
----------
STR1
STR2
STR3
STR4

So it can be easy used in subqueries with IN clause

SELECT * 
  FROM <TABLE_NAME>
 WHERE <COLUMN_ID> IN 
    (SELECT * FROM 
     TABLE(fn_split_converted('STR1,STR2,STR3,STR4', ','))

XML

Another option using xmltable method

SELECT (COLUMN_VALUE).getClobVal() str_rec
  FROM xmltable
       (
        ('"'||REPLACE('STR1,STR2,STR3,STR4', ',', '","')||'"')
       );

STR_REC               
-------------------
STR1
STR2
STR3
STR4

I believe that world as you know it is easier now ;o)

Have a fun 🙂

Tomasz

3 thoughts on “String split methods

  1. Great, short and comprehensive article.
    From my side, I would add an implementation of function without SELECT statement and without REGEXP, as SELECT + REGEXP is much slower than pure PL/SQL processing.

  2. We could also use XMLTABLE approach:
    SQL> select column_value
    2 from xmltable((‘”‘||replace(‘STR1,STR2,STR3,STR4’, ‘,’, ‘”,”‘)||'”‘));

    COLUMN_VALUE
    —————————————————————————–
    STR1
    STR2
    STR3
    STR4

Leave a Reply

Your email address will not be published. Required fields are marked *