HANA: Split string?
16,217
Solution 1
Try this,
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')
Solution 2
Another way of splitting a string is with an outbound 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;
Solution 3
Until the SP10, you need to create a function to SPLIT strings.
If your need is get some char, you can use SUBSTR()
On SP9 or later, you can work with REGEX
Author by
Peder Rice
Updated on June 28, 2022Comments
-
Peder Rice almost 2 years
Is there a way to split a string in HANA?
Something similar to the equivalent in SQL Server:
SELECT * FROM dbo.fnSplitString('valueA,valueB', ',')