Exclude the row which has numeric characters, only at the beginning of the row

user_13747728 0
Viewed 411

I have a similar table as below:

product      
01 apple
02 orange
banana 10

I am trying to exclude only rows which start with a number. If the number is not in the beginning then it should not be excluded. The desired table output should be like this:

product      
banana 10

However with my current query, it excludes everything as soon as there is a number in the row:

SELECT * 
FROM table 
WHERE product NOT LIKE '%0%'

Could anyone please suggest me on how to tackle this? Much appreciated.

amazon-redshift sql
3 Answers

Something like this maybe:

SELECT * 
FROM table 
WHERE left(product, 1) NOT IN ('0','1','2','3','4','5','6','7','8','9')
user_134735250

regex to match lines that don't start with number is

^[^0-9].*

An sql query in mysql would look like

SELECT * 
FROM table 
WHERE product RLIKE '^[^0-9].*'
user_142082620

I would recommend regular expressions. In Redshift, this looks like:

where product ~ '^[^0-9]'

I might also suggest:

where left(product, 1) not between '0' and '9'
user_11440350
Related