List of Words in a String using SQLScript for SAP HANA
This SAP HANA SQLScript tutorial shows how to get the list of words in a given string or sentence as resultset of a SELECT statement.
For this sample SQLScript code, we will use a global temporary table, While loop and SQLScript string functions Locate and Left.
First of all SQLScript developer will require a temporary table to store the words found in the given string variable.
In order to use the temporary table, we need to create it as global temporary table
The reason why I preferred global temporary table to local temporary table is that; we cannot insert data into temporary local tables.
The first section if for creation of the temporary table that I need formed of only one column containing a single word for each entry.
Then an anonymous block with WHILE loop is executed including the SQLScript codes to search for space character in the string variable.
String fragments between spaces are considered as words.
CREATE GLOBAL TEMPORARY TABLE gt_words(
DECLARE v_text NVARCHAR(100) := 'SAP HANA SQLScript tutorial by Kodyaz.com';
DECLARE v_search_str NVARCHAR(5) := ' ';
declare v_position int := 0;
declare v_loop tinyint := 1; -- boolean to control while loop
WHILE (v_loop = 1) DO
v_position := locate(:v_text, :v_search_str);
if (v_position = 0) then
v_loop := 0;
insert into gt_words values (v_text);
insert into gt_words values (left(v_text, v_position));
v_text := right(:v_text, length(:v_text) - :v_position);
select * from gt_words;
Here is the output of the above SQLScript on SQL Console
The SQLScript LOCATE string function is used to get the position of space characters in a given string variable.
By using the SQLScript LEFT string function, the string fragment on the left side of the space character is inserted into the temporary table as a new word record.