on 11-05-2013 10:46 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thank you for all your suggestions.
The simple solution I used was to check for VAL <='9', and it seems to work.
Thanks,
Hyun
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
Why not use LOCATE_REGEXPR?
LOCATE_REGEXPR('^(0|[1-9][0-9]*)$' in <Your_Col_Name>)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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');
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.