regex in SQL to detect one or more digit

Question

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?

Answer 1

Use REGEXP operator instead of LIKE operator

Try this:

SELECT '129387 store' REGEXP '^[0-9]* store$';

SELECT * FROM shop WHERE `name` REGEXP '^[0-9]+ store$';

Check the SQL FIDDLE DEMO

OUTPUT

|         NAME |
|--------------|
| 129387 store |

Answer 2

If you mean MySQL, LIKE does not implement regular expressions. It implements the much more restricted SQL pattern matching, which just has two special operators: % matches any sequence of characters, and _ matches any single character.

If you want regular expression matching, you must use the REGEXP or RLIKE operator:

SELECT *
FROM shop
WHERE name REGEXP '[0-9]+ store'

MySQL's regular expression language doesn't include \d to specify digits, but you could write that as:

SELECT *
FROM shop
WHERE name REGEXP '[[:digit:]]+ store'

If the store name must begin with digits, you need an anchor:

SELECT *
FROM shop
WHERE name REGEXP '^[0-9]+ store'

You can learn more about regular expression syntax at regular-expressions.info.

Answer 3

Try

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

Answer 4

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