how to check to see if a value is numeric in sap hana

[Former Member]

Hi,

Is there a SQL function or Calculation functions in HANA that
checks for a data type?

I need to check a column value to see if it contains numbers or characters,
but I don't see any functions that will do that.

Is there an IS_NUMERIC equivalent function is HANA?

Thank You,

Hyun Grasso

[yeushengteo · Accepted solution]

hi,

not sure if this will helps:

drop table bb;
create column table bb (col1 varchar(20));
insert into bb values ('123.45');
insert into bb values ('abcde.fg');
insert into bb values ('112.ee');
insert into bb values ('xwr.123');
insert into bb values ('-1BX.190');
insert into bb values ('+NB12.123');
insert into bb values ('  -1ZZCX.90');
insert into bb values ('+12.123  ');
insert into bb values (' -1299.9800  '); 
insert into bb values ('-30');

Select col1 as "Data",
       ltrim(col1,' +-.0123456789') as "Trim Data",
       length(ltrim(col1,' +-.0123456789')) as "Numeric Data is 0" from bb

Regards.

YS

[former_member252318]

Hi

Using the command proposed by Yeu Sheng Teo

I create the next Function

CREATE FUNCTION ISNUMERIC(Cadena VARCHAR)

RETURNS ESNUMERO INT

LANGUAGE SQLSCRIPT READS SQL DATA AS

--Funcion que Recrea el ISNUMERIC de SQL SERVER

--Retorna 1 Si la cadena pasada es un Numero

BEGIN

  ESNUMERO := length(ltrim(Cadena,'+-.0123456789'));

  IF ESNUMERO > 0

  then ESNUMERO := 0;

  Else ESNUMERO := 1;

  END IF;

END;

Then, we can use this Function in the same way of SQL SERVER

  Select ISNUMERIC('12345') from Dummy

                      Returns 1

Select ISNUMERIC('1234a5') from Dummy

                     Returns 0

  Select ISNUMERIC('abcdefg') from Dummy

                    Returns 0

Regards

ROB

[former_member686512]

Thanks sir this works like a champ.

[Former Member]

Thank you for all your suggestions.

The simple solution I used was to check for VAL <='9', and it seems to work.

Thanks,

Hyun

[former_member184768]

Hi,

I don't think it will work if you have data like '98AZR'.

Regards,

Ravi

[Former Member]

You could also consider creating your own scalar user-defined function (UDF). Keep in mind though that this function is computed per row, so performance could be bad depending on the size of your dataset (among other things).

Bottom of p. 38 here: http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

[jan_nyfeler2]

Why not use LOCATE_REGEXPR?

https://help.sap.com/docs/SAP_HANA_PLATFORM/4fe29514fd584807ac9f2a04f6754767/cb4866494bd647cd8926763...

LOCATE_REGEXPR('^(0|[1-9][0-9]*)$' in <Your_Col_Name>)

[former_member405173]

Hi,

you just have to use LOCATE_REGEXPR function.

You just need to know the size of your string.

SELECT LOCATE_REGEXPR(START '([[:digit:]]{YOUR_COLUMN_SIZE})' IN "YOUR_COLUMN" GROUP 1) "locate_regexpr" FROM DUMMY;

Example :

SELECT LOCATE_REGEXPR(START '([[:digit:]]{6})' IN '201401' GROUP 1) "locate_regexpr" FROM DUMMY;

For example will return 1 <=> a group of 6 digits <=> a number

when

SELECT LOCATE_REGEXPR(START '([[:digit:]]{6})' IN '201X01' GROUP 1) "locate_regexpr" FROM DUMMY;

will return 0.

Hope it's clear.

[feihua317]

I enhance your expression and please try with the following. It support negative number as well and no matter how long the field length is.

SELECT LOCATE_REGEXPR(START '(^-?[[:digit:]]+$)' IN '-20142101' GROUP 1) "locate_regexpr" FROM DUMMY;

[Former Member]

What about like this?

create procedure froggy.is_numeric(out is_numeric integer,in i_input varchar(100))

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    DEFAULT SCHEMA YANA_API

    AS   

    v_tmp integer;

BEGIN

   DECLARE EXIT HANDLER FOR SQLEXCEPTION

        BEGIN

            is_numeric := 0;

        END;

    select to_number(:i_input) into v_tmp from dummy;

    is_numeric := 1;

END;

call froggy.is_numeric(?,'K55.88');

[Former Member]

unfortunately there is no IS_NUMERIC equivalent in HANA..

One thing that should work as a workaround is create a function or procedure.. but that would be a little too much to do for small things like this..

Probably others experts might have some suggestion or work around to achieve it with in a SQL statement in HANA