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.
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:
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)) ;
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;
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.
If we modify the SQL script to use function parameter maxsplit as follows
lt_tbl := LIB:split_to_table( :lv_str, ' ', 1 );
The output of the split_to_table function with MAXSPLIT input parameter will be generated as in the below screnshot.
Please note, first of all we have created an alias for the SQLScript Library with USING clause
begin using SQLSCRIPT_STRING as LIB;
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( .... );
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 ... ;
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;
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.
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;
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 ' "" '
The SQL developer can limit the number of rows data by using MAX_RECORDS integer parameter of the Table_Summary function.
