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

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;
Code

SQLScript codes to find words list in string variable

Here is the output of the above SQLScript on SQL Console

SQLScript to list words in string variable

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;
Code

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;
Code

HANA SQLScript word count function

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"
Code

count words in string column using SQL function on HANA database table



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


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