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

SQLScript BuiltIn Library String Functions in SAP HANA SQL

With SAP HANA 2.0 SPS03 one of the new enhancements for SQLScript developers is the introduce of a new built-in SQLScript Library called SQLSCRIPT_STRING.
Now HANA database SQL programmers can use functions of following four SQLScript Library.

SQLSCRIPT_CACHE
SQLSCRIPT_PRINT
SQLSCRIPT_STRING
SQLSCRIPT_SYNC

When SQL developer launches SAP HANA Database Explorer and connects to a HANA database (resource), for example under SYS database they will see Libraries node.
Here is the screenshot of Database Explorer when I click the Libraries node showing 4 SQLScript libraries.

SAP HANA Database Explorer and SQLScript Libraries

If you double click on a SQLScript Library name, the CREATE statement of the Library with the SQL functions included in that Library will be displayed as follows:

HANA database SQL library SQLScript_String functions list

As programmers can see there are many functions predefined in SAP HANA database SQL library SQLScript_String functions list


SQLSCRIPT_STRING Library SPLIT_TO_TABLE Function

As an example, let's talk on SPLIT_TO_TABLE function first.
The definition, input and returning parameters and data types are defined as follows within the SQLScript Library SQLSCRIPT_STRING:

PUBLIC FUNCTION SPLIT_TO_TABLE(IN VALUE NVARCHAR(8388607), IN SEPARATOR NVARCHAR(8388607), IN MAXSPLIT INT DEFAULT -1) RETURNS TABLE(RESULT NVARCHAR(5000)) ;
Code

The SQLString string function SPLIT_TO_TABLE takes an input string parameter named VALUE.
This is the string that we want to split into pieces.
A second input parameter is SEPARATOR. This seperator string will be used to split the input VALUE string.
MAXSPLIT parameter is useful if the SQL developer is concerned with only a number of splitted strings. For example, the split can produce 10 rows each containing a string piece but if you're querying for the first 3, then you can pass three as MAXSPLIT parameter value.
What is interesting here is that the rest of the string is returned as an additional line. So return table will contain 4 rows :)
The default value is -1 indicating that the SPLIT_TO_TABLE function will return all splitted data as output in the return table.

The SPLIT_TO_TABLE function returns a table formed of a single column named Result of data type nvarchar(5000)

This definition can be confusing. Let's see how SQLScript developers can use SQLSCRIPT_STRING library SPLIT_TO_TABLE function with an example case.

do
begin using SQLSCRIPT_STRING as LIB;

declare lt_tbl table (result varchar(5000));
declare lv_str varchar(5000);

lv_str := 'Kodyaz.com: SQLScript BuiltIn Library Functions for SAP HANA SQL Developer';

lt_tbl := LIB:split_to_table( :lv_str, ' ' );

select * from :lt_tbl;

end;
Code

As SQL programmers can see, using builtin SQLScript_String Library function SPLIT_TO_TABLE, developer can split input string using space as a separator and return as table data.

SQLScript String library function split_to_table for HANA database developer

If we modify the SQL script to use function parameter maxsplit as follows

lt_tbl := LIB:split_to_table( :lv_str, ' ', 1 );
Code

The output of the split_to_table function with MAXSPLIT input parameter will be generated as in the below screnshot.

split_to_table SQLScript string function maxsplit parameter

Please note, first of all we have created an alias for the SQLScript Library with USING clause

begin using SQLSCRIPT_STRING as LIB;
Code

And then within the anonymous code block, developer can call functions included in the related SQLScript builtin library similar to below using the Library alias and function name concataneted with ":"

LIB:split_to_table( .... );
Code

Although the split_to_table function is a table valued function, as seen in the SQL code we don't build the query using the split_to_table() function in the FROM clause.


SQLSCRIPT_STRING Library SPLIT Function

Another string SQL function shipped with SQLSCRIPT_STRING Library is Split function.
The definition of the split function in the SQLScript library is as follows:

PUBLIC FUNCTION SPLIT(IN VALUE NVARCHAR(8388607), IN SEPARATOR NVARCHAR(8388607), IN MAXSPLIT INT DEFAULT -1) RETURNS ... ;
Code

With the experience from previous function, we can now assume that we have input string VALUE and additionally we have separator as the second input parameter.
The last input parameter is MaxSplit which is used to identify number of splitted string pieces that the developer is concerning. The function returns one more column for the rest of the string.
We will see it in action in following SQL sample

The RETURN parameter is not seen in the definition. That is because the function returns scalar values in number defined with MaxSplit (plus 1 more)

Here is a sample SQLScript for HANA database programmer

do
begin using SQLSCRIPT_STRING as LIB;

declare lv_string varchar(5000);

declare lv_string1 varchar(5000);
declare lv_string2 varchar(5000);
declare lv_string_rest varchar(5000);

lv_string := 'Kodyaz.com, SAP HANA Database Explorer, SQLScript Library, Tutorial';

(lv_string1, lv_string2, lv_string_rest) := LIB:split(:lv_string, ',', 2) ;

select lv_string1, lv_string2, lv_string_rest from dummy;

end;
Code

As expected, we have first two splitted string values in the lv_string1 and lv_string2 parameters. The rest is returned as lv_string_rest parameter value.

split string function sample SQL code on HANA database


TABLE_SUMMARY Function to Concatanate Table Columns

Another SQLScript_String Library function is Table_Summary function which returns the column names and values of all rows data of the input table as a string return value.

Here is a sample SQL script

do
begin using SQLSCRIPT_STRING as LIB;

declare lv_string nvarchar(5000);
declare lv_rowCount int = 3;
TBL = select table_name, table_oid from "TABLES";

lv_string := LIB:TABLE_SUMMARY( :TBL , :lv_rowCount);
select lv_string as "Table Names" from dummy;

end;
Code

HANA Database developers can execute above code and will see that the column names are at the beginning of the list separated by comma.
Right after column names of the table, column values are concatenated.
String values are between ' "" '

SQLScript String function Table_Summary on SAP HANA database

The SQL developer can limit the number of rows data by using MAX_RECORDS integer parameter of the Table_Summary function.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


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