SQL Server's isNumeric() equivalent in amazon redshift

By : Elm
Source: Stackoverflow.com
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

By : Elm


Answers

looks like what you are looking for the is the similar to function (Redshift doc)

where left(field,4) similar to [0-9]{4}
By : Ruben


where regexp_instr(field1,'^[0-9]{4}') = 0

will remove rows starting with 4 digits (the above regexp_instr will return 1 for the rows with field1 starting with 4 digits)



This video can help you solving your question :)
By: admin