Skip to Content
1
Former Member
Jul 04, 2014 at 01:16 PM

SQL Function to Split string according to pattern

1434 Views

Hi,

Want to split string value according to its spectator pattern.

For e.g Input string is 'A,B,C,' . Function should return table for this input string value as below.

No X 1 A 2 B 3

C

Have written below function, But its not working as expected.

CREATE FUNCTION FN_STRING_SPLIT (IP_STRING NVARCHAR(5000)) RETURNS TABLE (A NVARCHAR(50))

LANGUAGE SQLSCRIPT AS

V_OUT VARCHAR(100):='';

V_OUT1 VARCHAR(100):='';

V_SUBSTR VARCHAR(100):='';

V_SUBSTR1 VARCHAR(100):='';

V_START_INDEX INTEGER;

V_COUNT INTEGER:=0;

BEGIN

V_OUT:=SUBSTR_BEFORE(:IP_STRING,',');

WHILE(LENGTH(V_OUT)>0) DO

V_OUT1:=:V_OUT1||:V_OUT;

V_SUBSTR:=:V_SUBSTR||:V_OUT;

V_COUNT:=:V_COUNT+1;

/*V_START_INDEX:=LENGTH(IP_STRING)-LENGTH(V_SUBSTR);*/

V_SUBSTR1:=SUBSTR(:IP_STRING,LENGTH(:V_SUBSTR)+:V_COUNT,LENGTH(:IP_STRING));

V_OUT1:=:V_SUBSTR1;

V_OUT:=SUBSTR_BEFORE(:V_SUBSTR1,',');

/*V_OUT1 = SELECT * FROM :V_OUT1 UNION SELECT * FROM :V_OUT;*/

END WHILE;

RETURN select :V_OUT1 as A from dummy;

END;

Please suggest proper solution.

Thanks in advance.

regards,

Shweta