mySQL SUBSTRING with multiple conditions in WHERE clause

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

Korhan Ozturk · Accepted Answer · 2012-02-08 21:30:45Z

11

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

edited Feb 8, 2012 at 21:30

answered Feb 8, 2012 at 20:46

Korhan Ozturk

11.3k6 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Erik Ekman · Accepted Answer · 2012-02-08 20:45:49Z

6

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

answered Feb 8, 2012 at 20:45

Erik Ekman

2,08612 silver badges13 bronze badges

Comments

Marc B · Accepted Answer · 2012-02-08 20:47:18Z

4

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.

answered Feb 8, 2012 at 20:47

Marc B

362k44 gold badges433 silver badges508 bronze badges

Collectives™ on Stack Overflow

mySQL SUBSTRING with multiple conditions in WHERE clause

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related