CODEWITHSUNDEEP
sqlamazon-redshift
tlqn
tlqn

Reputation: 399

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

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.

Upvotes: 3

Views: 1341

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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'

Upvotes: 1

NechesStich
NechesStich

Reputation: 226

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].*'

Upvotes: 2

Sergey Geron
Sergey Geron

Reputation: 10152

Something like this maybe:

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

Upvotes: 3

Related Questions