mySQL SUBSTRING with multiple conditions in WHERE

Question

I have this query:

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'))

that gives me this error:

#1630 - FUNCTION Database.SUBSTRING does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

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')

Answer 1

You've got an extra space in the final clause:

AND (SUBSTRING (phone,5,4) BETWEEN '5400' AND '5499'))
              ^---here

remove that, and the whole query will work.

Answer 2

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.

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'))

Answer 3

You cannot have a space between SUBSTRING and (. There is a db option that allows it if you really want to.