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

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

SQLScript string functions Locate in SAP HANA

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

SAP HANA SQLScript string functions

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.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


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