Creating ISNUMBER function in Snowflake

Question

ISNUMBER is not a in-built function in Snowflake.

I want to create ISNUMBER function in Snowflake as an user-defined function.

Below is the user-defined function of ISNUMBER in oracle:

CREATE OR REPLACE FUNCTION is_number (p_string IN VARCHAR2)
  RETURN INT
IS
  v_num NUMBER;
BEGIN
  v_num := TO_NUMBER(p_string);
  RETURN 1;
EXCEPTION
WHEN VALUE_ERROR THEN
  RETURN 0;
END is_number;

I have tried in this way in Snowflake to create the function:

create or replace function ISNUMBER(p_string in varchar2) returns int language sql as $$ declare v_num number; begin v_num := TO_NUMBER(p_string); return 1; exception when statement_error then return 0; end; $$;

Help me by correcting the above code.

score 2 · Answer 1 · answered Dec 12 '22 at 11:50

Snowflake Scripting does not support UDFs for now.

https://docs.snowflake.com/en/developer-guide/snowflake-scripting/index.html

I think it's easier to do it like this:

  create or replace function ISNUMBER(p_string varchar2) returns int as 
    'iff( try_to_number(p_string) is null, 0, 1 )';

select isnumber( '232323'); -- returns 1

select isnumber( 'A343'); -- returns 0

Creating ISNUMBER function in Snowflake

1 Answers1