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