regex in SQL to detect one or more digit

user_721937 0
Viewed 25855

I have the following query:

SELECT * 
FROM  `shop` 
WHERE  `name` LIKE  '%[0-9]+ store%'

I wanted to match strings that says '129387 store', but the above regex doesn't work. Why is that?

sql-like select mysql sql regex
4 Answers

For those like me looking for Postgres:

Any digit:

SELECT * 
FROM  `shop` 
WHERE  `name` ~ '\d'

or

SELECT * 
FROM  `shop` 
WHERE  `name` ~ '[0-9]'

The OP question:

SELECT * 
FROM  `shop` 
WHERE  `name` ~ '^\d+ store$'

There is a "SIMILAR TO" option in Postgres (but not MySQL) as well (name SIMILAR TO '_*\d_*'), but apparently it's basically just syntactic'ish sugar for regexp so it's recommended to use regexp instead: https://dba.stackexchange.com/a/10696/16892

user_324530
Related