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 |