HANA Instr function to check for numbers?

[Former Member]

Hi,

I am trying to create a calculated column to check the validity of another field for the presence of numbers in the string.

For example

Partner     Tel_number     Valid

1               1flowers          Yes

2               Telephone       No 

3               6046060543    Yes

My Expected result would be for partner 1 and 2 to return valid results and partner 2 to be invalid.

What is the correct syntax for instr to do this?

I've already tried the following:

IF(INSTR("TEL_NUMBER",IN(0,1,2,3,4,5,6,7,8,9)),0,1)

IF(INSTR("TEL_NUMBER",'1' OR '2' ...... ),0,1)

Thanks!

John

[Former Member]

Hi Echo,

I think that with a slight modification in Lars code,  you can acieve it

After replacing all the numbers with empty string, if the modified string is still same as the original string then the original string doesn't contain any numbers.

instead of  ::

if length(:tmp_string)>0 then

    isNumeric := 0;

else  

    isNumeric := 1;

end if;

you can replace it with

if length(:tmp_string)= length(:checkString) then

    isNumeric := 0;

else  

    isNumeric := 1;

end if;

Thanks to Lars for the cool logic.

Regards,

Lalu George

[ChengJiajia]

Thanks all for your kindly suggestions.

[former_member205144]

This message was moderated.

[lbreddemann]

Hi John

as of SPS8 you could do something like this:

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

Cheers,

Lars

[ChengJiajia]

Hello Lars,

Thanks for your answer. But your function can only filter out the pure number. We looking for the equivalent logic in ORACLE to find the strings which includes numbers. Is there any way we can do in HANA ?

SELECT HM.HTH FROM ASMS.HT_MODIFIED_HEADERS HM

WHERE  regexp_like(HM.HTH,'([0-9])' ;

HTH

0007240704160A

0007240705080A

0000310602090K

0005040602230A

0003800602169A

0003800602169A

Thanks in advance

[lbreddemann]

Hey Echo,

as you and John are SAP employees just like me, you have access to the internal communities and of course the full stack of documentation.

You may want to look into these options first.

Currently, there is no regex function on SQL level in SAP HANA.

But looking at your - now changed -  requirement you should be able to extend the function to actually return the string if it is valid.

- Lars