Clicky

PL/SQL Convert String to Array Procedure

PL/SQL Convert String to Array Procedure

Any programmer when thinks about PL/SQL expects certain functionality by default to be provided by the programming language for rapid coding. This helps to concentrate more on the business part rather than the technical complexities.With that in mind I would like to share a simple topic of converting a string in to array in PL/SQL, which is simple but a yet a powerful tool to ease most of out programming burdens.

Challenge

PL/SQL does not provide a function to convert string in to array just like any other programming language, where in the string is delimited  by a special character say comma.

The attached procedure capabilities:
1) The procedure can split the string in to an array
2) if no delimiter is found it will return the string in array.

Execution

Once can use in the PL/SQL Code anf invoke them like,

SPLIT2ARRAY (‘1,2,3’, ‘,’) ;

The output will be an array [1,2,3].

The Procedure is as follows:

set serveroutput on ;
CREATE OR REPLACE PACKAGE STRING_FNC
IS

TYPE t_array IS TABLE OF VARCHAR2(3900)
INDEX BY BINARY_INTEGER;

FUNCTION SPLIT2ARRAY (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array;

END;
/

CREATE OR REPLACE PACKAGE BODY STRING_FNC
IS

FUNCTION SPLIT2ARRAY (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array
IS

i       number :=0;
pos     number :=0;
lv_str  varchar2(300) := p_in_string;

strings t_array;

BEGIN

— determine first chuck of string
pos := instr(lv_str,p_delim,1,1);
IF pos = 0 THEN
strings(1) := lv_str;
END IF ;

— while there are chunks left, loop
WHILE ( pos != 0) LOOP

— increment counter
i := i + 1;

— create array element for chuck of string
strings(i) := substr(lv_str,1,pos-1);

— remove chunk from string
lv_str := substr(lv_str,pos+1,length(lv_str));

— determine next chunk
pos := instr(lv_str,p_delim,1,1);

— no last chunk, add to array
IF pos = 0 THEN

strings(i+1) := lv_str;

END IF;

END LOOP;

— return array
RETURN strings;

END SPLIT2ARRAY;

END;
/
show error ;