SQL Server's isNumeric() equivalent in amazon redshift
Try something like:
where field1 ~ '^[0-9]{4}'
It will match any string, that starts with 4 digits.
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
It looks like the code you are looking for the is the similar to
function:
where left(field,4) similar to '[0-9]{4}'
Redshift doc