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 Regular Expression for Numeric or Letter Characters in String on SAP HANA


SQLScript developers can use regular expressions to find numeric characters or alpha-numeric characters within a given string variable or database table field. I use SQLScript LOCATE_REGEXPR string function to identify characters that match the regular expression for finding numeric or non-numeric characters in a given string value.


Regular Expression for Numeric Characters using SQL on SAP HANA Database

If as a database developer, you need to filter out only the numeric characters from a given string expressions using SQLScript SQL developers can use regular expression p{N} using Locate_RegExpr function as below in this tutorial.

Here is a sample SQLScript code sample that HANA database developers can use to see how SQL regular expression LOCATE_REGEXPR function is used within WHILE loop

do
begin

declare vResult nvarchar(100) default N'';
declare vString nvarchar(100);
declare vCharInd int default -1;

vString = N'1Kodyaz.com26N7a0ES0';

while vCharInd <> 0 do

select locate_regexpr(START '[\p{N}]' IN :vString ) into "VCHARIND" from dummy; -- numbers

if (:vCharInd > 0) then
select concat(:vResult, substring(:vString, :vCharInd, 1)) into "VRESULT" from dummy;
select substring(:vString, :vCharInd+1) into "VSTRING" from dummy;
end if;

end while;

select :vResult as "Numbers In String" from dummy;

end;
Code

The output of the above SQLScript code block will be as follows returning only the numeric characters contained within the input string

HANA SQLScript function Locate_RegExpr to find numeric characters

Unfortunately, it is not always handy to use a SQL script or SQL code block as above.
I tried to create a user-defined function.
Using SAP HANA Studio, I got following warning on on premise S/4HANA system: java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF
Although this seems to be a warning, it prevent me to get the results I expect from the user-defined function.
On cloud, when I create the same SQL function code I got no errors such "java.sql.SQLWarning: Not recommended feature: Using SELECT INTO in Scalar UDF", the execution of the UDF did not success to produce expected data.


Find Unicode Letter Characters using SQLScript

By replacing the p{N} (for numeric) with p{L} (for case-insensitive letters including unicode) in the regular expression, SQLScript developers can build an SQL code to identify letters (no numbers or special characters like punctuations) in a given string.

declare vResult nvarchar(100) default N'';
declare vString nvarchar(100);
declare vCharInd int default -1;

vString = N'7Kodyaz6.com1!';

while vCharInd <> 0 do
select locate_regexpr(START '[\p{L}]' IN :vString ) into "VCHARIND" from dummy; -- letters
if (:vCharInd > 0) then
select concat(:vResult, substring(:vString, :vCharInd, 1)) into "VRESULT" from dummy;
select substring(:vString, :vCharInd+1) into "VSTRING" from dummy;
end if;
end while;
select :vResult as "Only Letters In String" from dummy;
Code

As SQL programmers can see in below screenshot, the output "Kodyazcom" if fetched by filtering out all characters except letters including numeric characters and punctuation

SQLScript Locate_SQLExpr function for letters in a string


Use RegExp for Alpha-Numeric Characters with SQLScript

To get the alpha-numeric characters from a given string variable, SQLScript developer can use regular expression [^\p{N}] to filter all characters except numeric ones.
So the SELECT statement where Locate_RegExpr() function can be altered as follows to get only the alphanumeric characters within a given string variable.

select locate_regexpr(START '[^\p{N}]' IN :vString ) into "VCHARIND" from dummy; -- alphanumeric
Code


SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


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