SQL LIKE for numeric search

Question

I am working with some databases input with searching the data by alpha and numeric. I am using LIKE show the alpha and everything works well except for numeric input.

SELECT * FROM `product` WHERE `name` LIKE `[0-9]%`

Is there any mistake in the like wildcard I am using?

note: example for the data I am searching like "99 T-shirt model" so basically I just want to sort all name starting with a number and I do not care about the next letter.

Dmitry Bychenko · Accepted Answer · 2013-07-23T07:29:14.520

4

Usually, you can't mix regular expression with wild cards. Use either regular expression e.g. (Oracle version):

  select * 
    from Product
   where RegExp_Like(name, '^[0-9]') --<- Oracle version, see your DBMS regexp format, cause regular expressions are DBMS dependent

-- Oracle: RegExp_Like(name, '^[0-9]')
-- MySql: `name` REGEXP '^[0-9]'

or pure like, substring, comparisons etc.

  select * 
    from Product
   where (name >= '0') and (name <= '9')

edited Jul 23 '13 at 07:29

answered Jul 23 '13 at 06:16

Dmitry Bychenko

180,369
20
160
215

I'm guessing it's MySQL and not Oracle. – Phil Jul 23 '13 at 06:21
1

For MySql the regular expression is (`name` REGEXP '^[0-9]') – Dmitry Bychenko Jul 23 '13 at 06:25

SQL LIKE for numeric search

1 Answers1