Klaus' Blog

going public

    Advertisements
  • Categories

  • Archives

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

One Response to “Oracle split function”

  1. ragu hammond said

    My problem solved. Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: