SQL Server's isNumeric() equivalent in amazon redshift

Question

• I'm using amazon redshift as my data warehouse
• I have a field (field1)of type string. Some of the strings start with four numbers and others with letters:

'test alpha'
'1382 test beta'

• I want to filter out rows where the string does not start with four numbers
• Looking at the redshift documentation, I don't believe isnumber or isnumeric are functions. It seems that the 'like' function is the best possibility.

• I tried

  where left(field1, 4) like '[0-9][0-9][0-9][0-9]'

this did not work and from the link below seems like redshift may not support that:

https://forums.aws.amazon.com/message.jspa?messageID=439850

is there an error in the 'where' clause? if not and that clause isn't supported in redshift, is there a way to filter? I was thinking of using cast

cast(left(field1,4) as integer) 

and then passing over the row if it generated an error, but not sure how to do this in amazon redshift. or is there some other proxy for the isnumeric filter.

thanks

Answer 1

Try something like:

where field1 ~ '^[0-9]{4}'

It will match any string, that starts with 4 digits.

Answer 2

Although long time has passed since this question was asked I have not found an adequate response. So I feel obliged to share my solution which works fine on my Redshift cluster today (March 2016).

The UDF function is:

create or replace function isnumeric (aval VARCHAR(20000))
  returns bool
IMMUTABLE 
as $$
    try:
       x = int(aval);
    except:
       return (1==2);
    else:
       return (1==1);
$$ language plpythonu;

Usage would be:

select isnumeric(mycolumn), * from mytable
    where isnumeric(mycolumn)=false