SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

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.


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)
 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.

SQLScript split string using SubStr_Before and SubStr_After functions

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.


Install SAP Free
CRM Companies List
Web Based CRM Software

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.