PL/SQL Function To Split String Into Array

von Tobias Redmann

Im Standard PL/SQL Funktionsumfang gibt es keine Function die mir einen beliebigen String mit konfigurierbaren Delimiter in Teilstrings – vorzugsweise in einem ARRAY (z.B. Nested Table) – splittet.
Ich wollte eine reine PL/SQL Implementierung, welche aber auch in SQL benutzbar ist und eine beliebiges Trennzeichen zulässt.

Sourcecode der PL/SQL Function – f_split_string_into_array

Es muss eine TYPE in der Datenbank angelegt werden, damit die Function auch in einem SQL-Statement verwendet werden kann. Beispiele findet ihr weiter unten in diesem Artikel.

/** Nested Table Type */
CREATE OR REPLACE TYPE nt_split_result IS TABLE OF VARCHAR2(4000)
/

CREATE OR REPLACE FUNCTION f_split_string_into_array
(pi_str IN VARCHAR2
,pi_delimiter IN VARCHAR2
) RETURN nt_split_result
IS
/** Function extracts parts of a string which are enclosed with an individual delimiter
*
* @author Karsten Besserdich
* @company Besserdich & Redmann GmbH
* @email kb@besserdich-redmann.com
* @URL www.besserdich.com
* @Date 20.08.2015
*
* @param pi_str string to split
* @param pi_delimiter Delimter
* @return Nested Table with extracted strings
*
* You can use this function in PL/SQL and SQL!
*/
lv_list nt_split_result := nt_split_result();
lv_position INTEGER := 1;
lv_result INTEGER := 1;

BEGIN
-- String muss gefüllt sein
IF LENGTH(pi_str) != 0
THEN
LOOP
-- an welcher Stelle tritt Delimter ab der Postition X als nächstes auf
lv_result := INSTR(pi_str,pi_delimiter,lv_position);
-- weitergehen, ggf. mehrere Delimiter hintereinander im String
IF lv_result = lv_position THEN
lv_position := lv_position+1;
CONTINUE;
END IF;
-- einen Teilstring zwischen zwei Delimiter gefunden
IF lv_result > lv_position THEN
lv_list.extend;
lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,lv_result-lv_position);
lv_position := lv_result+1;
CONTINUE;
END IF;
-- was am Ende gefunden und dann beende die Loop
IF lv_result = 0 AND lv_position <= LENGTH(pi_str) THEN
lv_list.extend;
lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,LENGTH(pi_str)-lv_position+1);
EXIT;
END IF;
-- am Ende - beende die Loop
IF lv_position > LENGTH(pi_str) THEN
EXIT;
END IF;
END LOOP;
END IF;

RETURN lv_list;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END f_split_string_into_array;
/

/** Nested Table Type */

CREATE OR REPLACE TYPE nt_split_result IS TABLE OF VARCHAR2(4000)

CREATE OR REPLACE FUNCTION f_split_string_into_array

(pi_str IN VARCHAR2

,pi_delimiter IN VARCHAR2

) RETURN nt_split_result

/** Function extracts parts of a string which are enclosed with an individual delimiter

* @author Karsten Besserdich

* @company Besserdich & Redmann GmbH

* @email kb@besserdich-redmann.com

* @URL www.besserdich.com

* @Date 20.08.2015

* @param pi_str string to split

* @param pi_delimiter Delimter

* @return Nested Table with extracted strings

* You can use this function in PL/SQL and SQL!

lv_list nt_split_result := nt_split_result();

lv_position INTEGER := 1;

lv_result INTEGER := 1;

BEGIN

-- String muss gefüllt sein

IF LENGTH(pi_str) != 0

THEN

LOOP

-- an welcher Stelle tritt Delimter ab der Postition X als nächstes auf

lv_result := INSTR(pi_str,pi_delimiter,lv_position);

-- weitergehen, ggf. mehrere Delimiter hintereinander im String

IF lv_result = lv_position THEN

lv_position := lv_position+1;

CONTINUE;

END IF;

-- einen Teilstring zwischen zwei Delimiter gefunden

IF lv_result > lv_position THEN

lv_list.extend;

lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,lv_result-lv_position);

lv_position := lv_result+1;

CONTINUE;

END IF;

-- was am Ende gefunden und dann beende die Loop

IF lv_result = 0 AND lv_position <= LENGTH(pi_str) THEN

lv_list.extend;

lv_list(lv_list.last) := SUBSTR(pi_str,lv_position,LENGTH(pi_str)-lv_position+1);

EXIT;

END IF;

-- am Ende - beende die Loop

IF lv_position > LENGTH(pi_str) THEN

EXIT;

END IF;

END LOOP;

END IF;

RETURN lv_list;

EXCEPTION

WHEN OTHERS THEN

RAISE;

END f_split_string_into_array;

Setup Test

Zum Demonstrieren der PL/SQL Function f_split_string_into_array benötige ich eine Tabelle mit Testdaten.

-- create table with test data
CREATE TABLE TESTDATA
( ID NUMBER
, TEXT VARCHAR2(2000 BYTE)
);

Insert into TESTDATA (ID,TEXT) values ('1','eins;zwei');
Insert into TESTDATA (ID,TEXT) values ('2','three;four;');
Insert into TESTDATA (ID,TEXT) values ('3',';;;;;;;');
Insert into TESTDATA (ID,TEXT) values ('4',';;;one;;;two;;');
Insert into TESTDATA (ID,TEXT) values ('5','who;is;that');
Insert into TESTDATA (ID,TEXT) values ('6',null);
COMMIT;

-- create table with test data

CREATE TABLE TESTDATA

( ID NUMBER

, TEXT VARCHAR2(2000 BYTE)

);

Insert into TESTDATA (ID,TEXT) values ('1','eins;zwei');

Insert into TESTDATA (ID,TEXT) values ('2','three;four;');

Insert into TESTDATA (ID,TEXT) values ('3',';;;;;;;');

Insert into TESTDATA (ID,TEXT) values ('4',';;;one;;;two;;');

Insert into TESTDATA (ID,TEXT) values ('5','who;is;that');

Insert into TESTDATA (ID,TEXT) values ('6',null);

COMMIT;

Example – f_split_string_into_array in SQL

set serveroutput on

SELECT
x.column_value each_element
FROM
TESTDATA t
, TABLE(f_split_string_into_array(t.text,';')) x
WHERE 1=1
;

set serveroutput on

SELECT

x.column_value each_element

FROM

TESTDATA t

, TABLE(f_split_string_into_array(t.text,';')) x

WHERE 1=1

;

Example – f_split_string_into_array in PL/SQL

set serveroutput on

DECLARE
lv_list nt_split_result := nt_split_result();
BEGIN
--
FOR rec IN (SELECT * FROM TESTDATA) LOOP
lv_list := f_split_string_into_array(rec.text,';');

-- show result in STD-OUT
FOR i IN 1..lv_list.COUNT() LOOP
dbms_output.put_line(lv_list(i));
END LOOP;

END LOOP;
END;

set serveroutput on

DECLARE

lv_list nt_split_result := nt_split_result();

BEGIN

FOR rec IN (SELECT * FROM TESTDATA) LOOP

lv_list := f_split_string_into_array(rec.text,';');

-- show result in STD-OUT

FOR i IN 1..lv_list.COUNT() LOOP

dbms_output.put_line(lv_list(i));

END LOOP;

END;

Konstruktives Feedback ist ausdrücklich gewünscht.

6 KOMMENTARE

2015

von Reimzak

erfüllt das nicht den gleichen Zweck...und ist auch noch einfacher SELECT REGEXP_SUBSTR ('nase,katze,haus', '[^,]+', 1, LEVEL) TXT FROM DUAL CONNECT BY REGEXP_SUBSTR ('nase,katze,haus', '[^,]+', 1, LEVEL) is not null

2015

von Karsten Besserdich

Danke für den Tipp. Ist ne tolle Sache!!!!

2017

von srkg

I tried this for single character input string like "Y", it is going in infinite loop and causing hung threads

2017

von Jürgen Gremmels

Wenn in Zeile 53 (IF lv_result = 0 AND lv_position < LENGTH(pi_str) THEN) das '<' durch ein '<=' ersetzt wird, funktioniert es auch mit Listen aus einzelnen Zeichen. Hinweis: die Variable 'lv_length NUMBER;' wird nicht (mehr) gebraucht.

2018

von Karsten Besserdich

Danke für den Hinweis. Ich habe den Code aktualisiert :-)

2019

von Thomas

Ich habe eine weitere Anforderung: Wenn mein String in "Gänsefüschen" steht, soll er den "Strichpunkt" ignorieren. (z.B. csv Datei) Beispiel: 'a;b;"Text von c mit; Strickpunkt";d' Als Ergebnis sollen folgende Teilstrings erzeugt werden: a b "Text von c mit; Strickpunkt" d Eine Idee, wie man das noch implementieren kann?

SCHREIBEN SIE EINEN KOMMENTAR

Sie sind hier: