3

When I use square brackets with a % wildcard as below MySQL does not select any records starting with a number. Many examples across the internet state this as the correct use. Any suggestions? It doesn't work for a letter (a-d) range either. I'm running MySQL 3.2

SELECT * FROM customers WHERE lname LIKE '[0-9]%' ORDER BY lname ASC

or

SELECT * FROM customers WHERE lname LIKE '[a-d]%' ORDER BY lname ASC
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
user3142992
  • 31
  • 1
  • 2
  • 1
    Where did you see MySQL examples using that syntax? I don't believe it is supported and isn't mentioned in the [`LIKE` docs](http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like). A similar thing is supported by MSSQL... – Michael Berkowski Dec 29 '13 at 00:30
  • 1
    MySQL does support [`REGEXP/RLIKE`](http://dev.mysql.com/doc/refman/5.1/en/regexp.html) which can be used to achieve what you want. – Michael Berkowski Dec 29 '13 at 00:31
  • 1
    The SQL standard only defines two wildcards for the `LIKE` operator: `%` for multiple characters and `_` for a single character. Everything else is non-standard and specific to one DBMS. MySQL's `LIKE` operator does not support any non-standard extensions as far as I know. And besides: MySQL 3.2 is definitely outdated. You should really upgrade to a DBMS from this millennium. –  Dec 29 '13 at 00:37

2 Answers2

3

Although I do not believe MySQL supports regular-expression-like character classes in [] with a regular LIKE clause (nor can I find relevant documentation), MySQL does have a REGEXP/RLIKE operator with which you can construct a regular expression for comparable functionality.

SELECT * FROM customers WHERE lname REGEXP '^[0-9]' ORDER BY lname ASC
SELECT * FROM customers WHERE lname REGEXP '^[a-d]' ORDER BY lname ASC

To build a regular expression similar to the wildcard patterns you used, start with ^ to left-anchor the pattern, and use the same character class [0-9], [a-f] as you proposed. You do not need to follow it by anything, because the % wildcard would be equivalent to a match of any zero or more characters following the initial left-anchored letter or number matched by ^[].

Of course, you can combine those statements with a logical OR, or build a regular expression which matches either case.

SELECT * FROM customers WHERE lname REGEXP '^[a-d]|[0-9]' ORDER BY lname ASC

Here's a demonstration.

One thing to keep in mind however: MySQL will not be able to use any index you may have on lname when using REGEXP.

n.b. I believe MS SQL Server supports a syntax similar to what you proposed.

Community
  • 1
  • 1
Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
1

I think in Mysql you can do something like this 

SELECT * FROM customers WHERE lname REGEXP '^[a-d]'

MySql Fiddle

M.Ali
  • 67,945
  • 13
  • 101
  • 127