SQL Function to Split string according to pattern

[Former Member]

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

[Former Member · Accepted solution]

Hi Sreehari,

Thank you 🙂

Yes have used UNNEST and Array.

Above code is working properly.

regards,

Shweta

[sreehari_vpillai · Accepted solution]

Welcome Shweta, I am seeing lot of calculations by the way

Sree

[danielschindler_com · Accepted solution]

This message was moderated.

[sreehari_vpillai]

Hi Shweta,

It was interesting . I tried it out and below is the code.

create type "SPLIT_TYPE" as table(
 SPLIT varchar(5)
);
drop procedure SPLIT_AT_COMMA;

CREATE PROCEDURE SPLIT_AT_COMMA(IN IP_STR VARCHAR(100),OUT OUTTAB SPLIT_TYPE) 
LANGUAGE SQLSCRIPT
AS
TEMP_STR VARCHAR(100) := :IP_STR || ',';
OUT_VAR VARCHAR(5) ARRAY;
POS INTEGER :=1;
FLAG INTEGER := 1;
LEFT_STR VARCHAR(5);
BEGIN
 WHILE(LENGTH(:TEMP_STR) > 0 ) DO
  LEFT_STR := SUBSTR_BEFORE (:TEMP_STR,',');
  TEMP_STR := SUBSTR_AFTER (:TEMP_STR,:LEFT_STR || ',');
  OUT_VAR[POS] := LEFT_STR;
  POS := :POS + 1;
 END WHILE;
 OUTTAB = UNNEST(:OUT_VAR) AS ("SPLIT");
END;
call "SPLIT_AT_COMMA" ('A,B,C,D',?)

Sreehari

[Former Member]

Nice. Yes UNNEST.

[Former Member]

Hi shweta,

        you are taking input a,b,c take this string any variable than read the string when the , sparator is occured than split it.

[Former Member]

How about using a temporary table?

[Former Member]

Hi Srihari,

Thanks for your reply.

Have tried to use temporary table in function and included insert command in function to insert value in temporary table. But Insert feature is not supported in Read only Function.

regards,

Shweta

[sreehari_vpillai]

UNNEST would help in such cases .

Sreehari