Skip to Content
avatar image
Former Member

HANA Instr function to check for numbers?

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 23, 2014 at 06:15 PM

    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)1blablupp0             1234    1             bla123  0            

    Cheers,

    Lars

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Nov 15, 2014 at 12:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded