In this article, I am giving an example of a function which I mostly use to split a string in PL/SQL. Especially, when I am writing a program to import a delimited file. Here are that function and an example of how to use it.
PL/SQL Function to Split a String
This function takes four parameters, and the details of these parameters are as under:
- Source_String: String to Split
- Field_Position: Delimiter Position
- UnTerminated: False by default, but if you pass True, then it will add the specified delimiter at the end of the string.
- Delimiter: Default is Comma ',', you can pass any other delimiter.
CREATE OR REPLACE FUNCTION GetString ( Source_string IN VARCHAR2, Field_position IN NUMBER, UnTerminated IN BOOLEAN DEFAULT FALSE, Delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS iPtrEnd PLS_INTEGER := 0; iPtrStart PLS_INTEGER := 0; vcSourceStrCopy VARCHAR2 (4000) := Source_string; BEGIN IF UnTerminated THEN vcSourceStrCopy := vcSourceStrCopy || Delimiter; END IF; IF Field_Position > 1 THEN iPtrStart := INSTR (vcSourceStrCopy, Delimiter, 1, Field_Position - 1) + LENGTH (Delimiter); ELSE iPtrStart := 1; END IF; iPtrEnd := INSTR (vcSourceStrCopy, Delimiter, 1, Field_Position); RETURN SUBSTR (vcSourceStrCopy, iPtrStart, (iPtrEnd - iPtrStart)); END GetString;
Example: Use This Function in PL/SQL Program
SET SERVEROUTPUT ON;
DECLARE
v_string VARCHAR2 (1000) := 'Java|.Net|C|Oracle|PHP';
TYPE t_string IS TABLE OF VARCHAR2 (1000)
INDEX BY BINARY_INTEGER;
t_str_array t_string;
BEGIN
FOR i IN 1 .. 5
LOOP
t_str_array (i) :=
getstring (v_string,
i,
TRUE,
'|');
END LOOP;
FOR j IN t_str_array.FIRST .. t_str_array.LAST
LOOP
DBMS_OUTPUT.put_Line (t_str_array (j));
END LOOP;
END;
/Output:
Java .Net C Oracle PHP PL/SQL procedure successfully completed.
