What is the equivalent of T-SQL ISNUMERIC function in HANA Sqlscript?

Question

I have a requirement where I need to convert all SQL Server stored procedures into HANA stored procedures. I have come across a function ISNUMERIC in T-SQL and I am not getting the equivalent of it in HANA.

After searching the web, I found that HANA does not have built in ISNUMERIC equivalent function. Then I tried writing my own function to achieve this and there I stuck with error handling and regular expression limitations.

My HANA version is 70.

Solution 1

SAP HANA does not come with a ISNUMERIC() function. However, this question had been asked and answered multiple times on SCN: E.g. http://scn.sap.com/thread/3449615

or my approach from back in the days: http://scn.sap.com/thread/3638673

drop function isnumeric;
create function isNumeric( IN checkString NVARCHAR(64))
returns isNumeric integer
language SQLSCRIPT as
begin
declare tmp_string nvarchar(64) := :checkString;
declare empty_string nvarchar(1) :='';

/* replace all numbers with the empty string */
tmp_string := replace (:tmp_string, '1', :empty_string);
tmp_string := replace (:tmp_string, '2', :empty_string);
tmp_string := replace (:tmp_string, '3', :empty_string);
tmp_string := replace (:tmp_string, '4', :empty_string);
tmp_string := replace (:tmp_string, '5', :empty_string);
tmp_string := replace (:tmp_string, '6', :empty_string);
tmp_string := replace (:tmp_string, '7', :empty_string);
tmp_string := replace (:tmp_string, '8', :empty_string);
tmp_string := replace (:tmp_string, '9', :empty_string);
tmp_string := replace (:tmp_string, '0', :empty_string);

/*if the remaining string is not empty, it must contain non-number characters */
if length(:tmp_string)>0 then
    isNumeric := 0;
else  
    isNumeric := 1;
end if;

end;

Testing this shows: with data as( select '1blablupp' as VAL from dummy union all select '1234' as VAL from dummy union all select 'bla123' as val from dummy)

select val, isNumeric(val)  from data 

VAL         ISNUMERIC(VAL)
1blablupp   0            
1234        1            
bla123      0   

Solution 2

From SAP HANA 1.0 SPS12 you can use regular expressions in SQL. You can use LIKE_REGEXPR function to check if specific string contains letters i.e.:

SELECT 
  CASE WHEN '0001A' LIKE_REGEXPR '[A-Z]' THEN 0 ELSE 1 END 
FROM 
  DUMMY;