Klaus' Blog

going public

    Advertisements
  • Categories

  • Archives

Archive for June, 2010

Oracle split function

Posted by klauskurz on 4. June 2010

This function splits a string by the given regex in Oracle 11g

The Type:


CREATE OR REPLACE TYPE str_array AS TABLE OF varchar2(1024 char);

The Code:


CREATE OR REPLACE  FUNCTION SPLIT
  (
    i_str      IN VARCHAR2,
    i_regex    IN VARCHAR2,
    i_modifier IN VARCHAR2 DEFAULT NULL
  ) RETURN str_array IS
 
    l_str_array str_array;
    l_count     NUMBER;
 
  BEGIN
 
    l_count := regexp_count(i_str,
                            i_regex,
                            1,
                            i_modifier);
 
    SELECT CAST(MULTISET (SELECT CASE
                          WHEN l_count = 0 THEN
                           i_str
                          WHEN LEVEL = l_count + 1 THEN
                           regexp_substr(i_str,
                                         '.*(' || i_regex || ')(.*)',
                                         1,
                                         1,
                                         i_modifier,
                                         2)
                       
                          ELSE
                           regexp_substr(i_str,
                                         '(.*?)(' || i_regex || ')',
                                         1,
                                         LEVEL,
                                         i_modifier,
                                         1)
                        END AS SPLIT
                   FROM dual
                 CONNECT BY LEVEL <= l_count + 1) AS str_array)
      INTO l_str_array
      FROM dual;
 
    RETURN l_str_array;
 
  END;

The Test:


SELECT COLUMN_VALUE, 1 FROM TABLE(SPLIT('1 = 1  AND  2 = 2 AND 3 = 3 AND 4 = 4',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 2 FROM TABLE(SPLIT('x = x',  '[[:blank:]]+AND[[:blank:]]+'  ))
UNION ALL
SELECT COLUMN_VALUE, 3 FROM TABLE(SPLIT('a,b,c,d,e,f,g,h',  ','  ))
UNION ALL
SELECT COLUMN_VALUE, 4 FROM TABLE(SPLIT('there is a dog and another dog!',  'dog'  ))
; 

 The Result:

1 = 1 1
2 = 2 1
3 = 3 1
4 = 4 1
x = x 2
a 3
b 3
c 3
d 3
e 3
f 3
g 3
h 3
there is a 4
 and another 4
! 4

Links:

http://www.oracle.com/global/de/community/tipps/regexp/index.html

http://www.oracle-developer.net/display.php?id=508

http://glosoli.blogspot.com/2006/07/oracle-plsql-function-to-split-strings.html

http://sadique-sadiqulislam.blogspot.com/2010/02/split-function-for-oracle-10g.html

Advertisements

Posted in oracle | 1 Comment »