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
当我使用带有%通配符的方括号时,MySQL不会选择任何以数字开头的记录。互联网上的许多例子都说这是正确的用法。有什么建议么?它也不适用于字母(a-d)范围。我正在运行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
2 个解决方案
#1
2
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.
虽然我不相信MySQL支持带有常规LIKE子句的[]中的类似正则表达式的字符类(我也找不到相关文档),但MySQL确实有一个REGEXP / RLIKE运算符,您可以使用它来构造可比功能的正则表达式。
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 ^[].
要构建类似于您使用的通配符模式的正则表达式,请从^开始左锚定模式,并使用与您建议的相同的字符类[0-9],[a-f]。您无需遵循任何操作,因为%通配符将等同于^ []匹配的初始左锚定字母或数字之后的任何零个或多个字符的匹配。
Of course, you can combine those statements with a logical OR, or build a regular expression which matches either case.
当然,您可以将这些语句与逻辑OR组合,或者构建一个与任一情况匹配的正则表达式。
SELECT * FROM customers WHERE lname REGEXP '^[a-d]|[0-9]' ORDER BY lname ASC
这是一个演示。
One thing to keep in mind however: MySQL will not be able to use any index you may have on lname when using REGEXP.
但要注意的一件事是:MySQL在使用REGEXP时将无法使用lname上的任何索引。
n.b. I believe MS SQL Server supports a syntax similar to what you proposed.
注:我相信MS SQL Server支持类似于您提议的语法。
#2
1
I think in Mysql you can do something like this
我认为在Mysql中你可以做这样的事情
SELECT * FROM customers WHERE lname REGEXP '^[a-d]'
#1
2
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.
虽然我不相信MySQL支持带有常规LIKE子句的[]中的类似正则表达式的字符类(我也找不到相关文档),但MySQL确实有一个REGEXP / RLIKE运算符,您可以使用它来构造可比功能的正则表达式。
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 ^[].
要构建类似于您使用的通配符模式的正则表达式,请从^开始左锚定模式,并使用与您建议的相同的字符类[0-9],[a-f]。您无需遵循任何操作,因为%通配符将等同于^ []匹配的初始左锚定字母或数字之后的任何零个或多个字符的匹配。
Of course, you can combine those statements with a logical OR, or build a regular expression which matches either case.
当然,您可以将这些语句与逻辑OR组合,或者构建一个与任一情况匹配的正则表达式。
SELECT * FROM customers WHERE lname REGEXP '^[a-d]|[0-9]' ORDER BY lname ASC
这是一个演示。
One thing to keep in mind however: MySQL will not be able to use any index you may have on lname when using REGEXP.
但要注意的一件事是:MySQL在使用REGEXP时将无法使用lname上的任何索引。
n.b. I believe MS SQL Server supports a syntax similar to what you proposed.
注:我相信MS SQL Server支持类似于您提议的语法。
#2
1
I think in Mysql you can do something like this
我认为在Mysql中你可以做这样的事情
SELECT * FROM customers WHERE lname REGEXP '^[a-d]'