on 07-04-2014 2:16 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
How about using a temporary table?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.