- 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:
'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.
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:
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.