SQLScript Locate String Function for SAP HANA SQL Developer

SAP SQLScript developers use LOCATE SQL string function to find the position of a substring in an other given string value. SQL programmers can use the string function Locate to search for an explicit text within an other longer text variable or in a string type table column. An other use of Locate for SAP HANA developers is to split text objects to find the exact place of delimiter characters.

Here is sample SQL code where Locate string function is used with is basic form but with special string variables in SQLScript by SAP HANA developer


SELECT

 LOCATE('', null),

 LOCATE(null, null),

 LOCATE('', ''),

 LOCATE(null, 'string'),

 LOCATE('string', null)

FROM DUMMY;

Code

As HANA developers will realize easily, whenever one of the arguments is NULL then the return value of the string function Locate() is NULL too.

If the string to search is empty string '' then the Locate function returns the first character by its return value as 1

Here is the outputs of the sample SQLScript code where string function Locate is used on SQL Console

SQLScript Locate string function example

In fact, the SAP HANA SQLScript Locate string function takes two optional arguments which provide more flexibility to SQL developers.


-- SQL Locate string function syntax

LOCATE (<haystack>, <needle>, <start_position>, <occurrences>)

Code

Locate function haystack argument is the main string object where the needle argument is being searched for.
start_position argument enables the developer to search the needle within haystack beginning from a certain numbers of characters from left to the end of the haystack
occurrences argument enables the search for the nth occurence of the needle within haystack

Let's make a few more SQLScript examples to understand the Locate function arguments.
I executed below SQLScript code anonymous block on SQL Console using SAP HANA Web-based Development Workbench


DO BEGIN

 DECLARE v_haystack NVARCHAR(5000);

 DECLARE v_needle CHAR(1) := ',';

 DECLARE v_start_position INT;

 DECLARE v_occurence INT;



 v_haystack := 'kodyaz,sqlscript,sap,hana,database,sql';


	
 -- returns first position of needle in haystack from the beginning

 select

  -- first needle position

  locate(v_haystack, v_needle) as firstPosition,

  -- first string seperated with needle character

  left(v_haystack, locate(v_haystack, v_needle) - 1) as firstString

 from dummy;


	
 select

  -- first needle position

  locate(v_haystack, v_needle) as firstPosition,

  -- second needle position

  locate(v_haystack, v_needle,0,2) as secondPosition,

  -- first string seperated with needle character

  left(v_haystack, locate(v_haystack, v_needle) - 1) as firstString,

  -- second string

  substring(

   v_haystack,

   locate(v_haystack, v_needle) + 1,

   locate(v_haystack, v_needle,0,2) - locate(v_haystack, v_needle) - 1

  ) as secondString

 from dummy;



 select

  -- third needle position

  locate(v_haystack, v_needle,0,3) as thirdPosition,

  -- next needle position

  locate(v_haystack,

   v_needle,

   locate(v_haystack, v_needle,0,3)+1, -- starting from 3rd needle pos

  1) as nextPosition,

  -- fourth string

  substring(v_haystack,21+1,26-1-21) as fourthString

 from dummy;



END;

Code

Let's check the outputs of each SQLScript Select statements executed on SQL Console


select

 -- first needle position

 locate(v_haystack, v_needle) as firstPosition,

 -- first string seperated with needle character

 left(v_haystack, locate(v_haystack, v_needle) - 1) as firstString

from dummy;

Code

First seperator position is found with SQL Locate() function and the string up to that position is fetched with SQLScript Left() string function as the first string piece

Below SQL Select output is very similar with above one.
With one difference, SQLScript SubString function is used for fetching the second string.


select

 -- first needle position

 locate(v_haystack, v_needle) as firstPosition,

 -- second needle position

 locate(v_haystack, v_needle,0,2) as secondPosition,

 -- first string seperated with needle character

 left(v_haystack, locate(v_haystack, v_needle) - 1) as firstString,

 -- second string

 substring(

  v_haystack,

  locate(v_haystack, v_needle) + 1,

  locate(v_haystack, v_needle,0,2) - locate(v_haystack, v_needle) - 1

 ) as secondString

from dummy;

Code

On the last SQL Select statement and its output is here.
SQLScript Locate() string function is used with start_position and occurrences input arguments.


select

 -- third needle position

 locate(v_haystack, v_needle,0,3) as thirdPosition,

 -- next needle position

 locate(v_haystack,

  v_needle,

  locate(v_haystack, v_needle,0,3)+1, -- starting from 3rd needle pos

 1) as nextPosition,

 -- fourth string

 substring(v_haystack,21+1,26-1-21) as fourthString

from dummy;

Code

SQLScript Locate SQL string function

SQLScript developers can refer to SAP Help Portal for more information on SQL string function Locate in SQLScript

For the SQLScript Left string function which I have used in these SQL scripts frequently, SAP HANA database developers can refer to given tutorial.