Split String using SQLScript SubStr Functions on SAP HANA Database
Using SAP HANA SQLScript functions SUBSTR_AFTER() and SUBSTR_BEFORE, SQL developers can split string variables using a defined delimeter character. SQLScript string functions SubStr_Before and SubStr_After enables programmers split string values easier than string splitting using character by character loop through the string character list.
If you are developing SQL using SQLScript on SAP HANA databases you will frequently require to split string values either stored in database columns or as a seperate SQL variable according to a split delimeter character.
Following SQLScript code block can be executed as an anonymous block on HANA database and will return each splitted string fragment as output.
Let's run the following SQLScript code to see what it produces.
do
begin
declare str string;
declare delimeter string := ',';
declare splitted string;
declare removeEmpty char(1) := 'X';
str := 'SAP,HANA,database,SQLScript,SQL';
if right(:str,1) <> delimeter then
str := :str || delimeter;
end if;
while :str <> SUBSTR_AFTER (:str,delimeter)
do
splitted = SUBSTR_BEFORE (:str,delimeter);
str = SUBSTR_AFTER (:str,delimeter);
if not(removeEmpty = 'X' and splitted = '') then
select splitted splitted, str remaining from dummy;
end if;
end while;
end; -- anonymous block
You will see that the SQL script returns 5 result sets since there are 5 splitted string fragments
SQL code block starts with variable declarations using SQLScript data types.
Since I use Substr_Before and Substr_After functions, I need to add delimeter character at the end of the string variable.
Before adding the delimeter character to the end of the input string, I read the last characters in the string using SQLScript Right() function.
If the last character is not the delimeter variable, I append it to the end else there is no need to append it again.
Then within WHILE loop, I read and return the string part before delimeter using SUBSTR_AFTER
The remaining part which is available to developer with SUBSTR_AFTER SQLScript function is again splitted within the WHILE loop next step.
SAP HANA SqlScript developers can refer to SAP Help Portal for official documentation on SUBSTR_BEFORE function.
If you want to refer to SUBSTR_AFTER() function documentation you can visit SAP Help.
