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 SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




CRM Companies List
Web Based CRM Software


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;

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.






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems