HANA: Split string?
4 answers
Try it,
CREATE PROCEDURE SPLIT_TEST(TEXT nvarchar(100))
AS
BEGIN
declare _items nvarchar(100) ARRAY;
declare _text nvarchar(100);
declare _index integer;
_text := :TEXT;
_index := 1;
WHILE LOCATE(:_text,',') > 0 DO
_items[:_index] := SUBSTR_BEFORE(:_text,',');
_text := SUBSTR_AFTER(:_text,',');
_index := :_index + 1;
END WHILE;
_items[:_index] := :_text;
rst = UNNEST(:_items) AS ("items");
SELECT * FROM :rst;
END;
CALL SPLIT_TEST('A,B,C,E,F')
+6
source to share
Another way to split a row is outgoing variable using table types:
CREATE TYPE UTIL_VARCHAR_LIST AS TABLE
(
VarcharValue Varchar(5000)
);
CREATE PROCEDURE UTIL_SPLIT_STRING
(
IN iv_split_string Varchar(5000),
IN iv_split_character Varchar(1) DEFAULT ',',
OUT ot_string_list UTIL_VARCHAR_LIST
) LANGUAGE SQLSCRIPT
AS
BEGIN
DECLARE TEMP_STR VARCHAR(5000) := :iv_split_string || :iv_split_character;
DECLARE OUT_VAR VARCHAR(5000) ARRAY;
DECLARE POS INTEGER :=1;
DECLARE FLAG INTEGER := 1;
DECLARE LEFT_STR VARCHAR(5000);
WHILE(LENGTH(:TEMP_STR) > 0 )
DO
LEFT_STR := SUBSTR_BEFORE (:TEMP_STR,:iv_split_character);
TEMP_STR := SUBSTR_AFTER (:TEMP_STR,:LEFT_STR || :iv_split_character);
OUT_VAR[POS] := LEFT_STR;
POS := :POS + 1;
END WHILE;
ot_string_list = UNNEST(:OUT_VAR) AS ("VARCHARVALUE");
END;
+2
source to share
I know this question is a little outdated, but I haven't found a good solution. So I created a UDF table for this, and I think it's better than a procedure call for this, since you don't need to create an additional variable. Well here is the code, hope it helps!
Credits go to Peder Rice since I just adapted his code. Hooray!
FUNCTION "TF_SPLIT_STRING"
(
i_string nvarchar(255)
)
RETURNS table(type char(1), value nvarchar(255))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
--> DECLARATIONS
-- The remaining string after checking for the delimiter
declare v_remaining VARCHAR(256) := :i_string;
-- The current vale that is being handled
declare v_current VARCHAR(255) := '';
-- The current positions of the resulting arrays
declare v_pos_include INTEGER := 1;
declare v_pos_exclude INTEGER := 1;
-- The arrays containing the results
declare a_include VARCHAR(12) array := ARRAY(0);
declare a_exclude VARCHAR(12) array := ARRAY(0);
-- The delimiter and the excluder
declare v_delimiter char(1) := ',';
declare v_excluder char(1) := '-';
--> SETUP BLOCK
if substr(:v_remaining, length(:v_remaining), 1) <> :v_delimiter
then
v_remaining := :v_remaining || :v_delimiter;
end if;
--> EXECUTION BLOCK
-- While there is a string to process
-- -> Split the string into the current value and the remaining
-- -> Check if there is an excluder character in the current value
-- -> If an excluder character is found, push the current value,
-- without the excluder, to the resulting array of excluding values
-- -> If no excluder is found, push the value to the resulting array
-- of including values
-- -> The corresponding position variable is incremented accordingly
while( LENGTH(:v_remaining) > 0 )
do
v_current := SUBSTR_BEFORE (:v_remaining,:v_delimiter);
v_remaining := SUBSTR_AFTER (:v_remaining, :v_current || :v_delimiter);
if substring(:v_current, 1, 1) = :v_excluder
then
a_exclude[v_pos_exclude] := SUBSTR_AFTER (:v_current, :v_excluder);
v_pos_exclude := :v_pos_exclude + 1;
else
a_include[v_pos_include] := v_current;
v_pos_include := :v_pos_include + 1;
end if;
end while;
--> ASSIGNMENTS BLOCK
-- format counter and divisions as table
o_include = unnest(:a_include) AS ("VALUE");
o_exclude = unnest(:a_exclude) AS ("VALUE");
return
select 'I' as type, value from :o_include
union all
select 'E' as type, value from :o_exclude
;
END;
0
source to share