在 WHERE 子句中有多个条件的 mySQL SUBSTRING [英] mySQL SUBSTRING with multiple conditions in WHERE clause

问题描述

我有这个查询:

SELECT DISTINCT phone, department 
FROM `users` 
WHERE ((SUBSTRING(phone,1,3) = '399') 
AND (SUBSTRING(phone,5,4) BETWEEN '3400' AND '3499')
OR  (SUBSTRING(phone,1,3) = '244') 
AND (SUBSTRING (phone,5,4) BETWEEN '5400' AND '5499'))

这给了我这个错误:

#1630 - FUNCTION Database.SUBSTRING 不存在.检查参考手册中的函数名称解析和解析"部分

如果我只使用这个查询就可以工作:

The query works if I only use this:

SELECT DISTINCT phone, department 
FROM `users` 
WHERE (SUBSTRING(phone,1,3) = '399') 
AND (SUBSTRING(phone,5,4) BETWEEN '3400' AND '3499')

推荐答案

根据 MySQL 函数名解析 内置函数名(在你的例子中是'SUBSTRING')和后面的("括号字符之间不能有空格.这是一个默认的解析器行为，用于区分内置函数的名称被用作函数调用或非表达式上下文中的标识符.

According to the MySQL Function Name Parsing there must be no whitespace between the built-In function name ('SUBSTRING' in your case) and the following "(" parenthesis character. It's a default parser behaviour to distinguish whether names of the built-in functions are being used as function calls or as identifiers in nonexpression context.

因此，如果您在最后一行中删除SUBSTRING"函数调用后的空格，您的查询将正常工作:

So if you remove the white space after 'SUBSTRING' function call in the last line, your query will work fine:

SELECT DISTINCT phone, department 
FROM `users` 
WHERE ((SUBSTRING(phone,1,3) = '399') 
AND (SUBSTRING(phone,5,4) BETWEEN '3400' AND '3499')
OR  (SUBSTRING(phone,1,3) = '244') 
AND (SUBSTRING(phone,5,4) BETWEEN '5400' AND '5499'))

这篇关于在 WHERE 子句中有多个条件的 mySQL SUBSTRING的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！