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(
word NVARCHAR(100)
);
DO BEGIN
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);
else
insert into gt_words values (left(v_text, v_position));
v_text := right(:v_text, length(:v_text) - :v_position);
end if;
END WHILE;
select * from gt_words;
END;
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.
HANA SQLScript Function to Count Words
Above SQLScript code can be converted into a SQL function that will return the number of words in a given input string parameter.
Here is the source codes of HANA Database SQLScript function used for word count named also WordCount
create or replace function WordCount (
v_text nvarchar(5000)
)
returns ev_wordcount int
language sqlscript
sql security invoker
as
begin
DECLARE v_search_str NVARCHAR(5) := ' ';
declare v_position int := 0;
declare v_loop tinyint := 1; -- boolean to control while loop
ev_wordcount := 0;
WHILE (v_loop = 1) DO
v_position := locate(:v_text, :v_search_str);
if (v_position = 0) then
v_loop := 0;
ev_wordcount := :ev_wordcount + 1;
else
v_text := right(:v_text, length(:v_text) - :v_position);
ev_wordcount := :ev_wordcount + 1;
end if;
END WHILE;
end;
This SQLScript function can be used as seen in following SQL examples. First sample is counting words in input string parameter.
select WordCount('Hi! My name is Eralper.') as "Count of Words" from dummy;
Second SQL sample is to count words in string column contents on a SAP HANA database table using our SQLScript function WordCount()
select
description, WordCount(description) as "Word Count"
from "SYS"."M_SYSTEM_INFORMATION_STATEMENTS"
