frosty frosty - 5 months ago 11
SQL Question

Selecting all strings from database that starts with a lowercase letter

I'm trying to select all strings in my database that starts with a lowercase letter with regexp, but for some reason it's selecting all the strings that starts with a uppercase letter too. What am I doing wrong?

SELECT *
FROM `allData`
WHERE response REGEXP '^[a-z]'
LIMIT 0 , 30

Answer

From the MySQL REGEXP manual:

REGEXP is not case sensitive, except when used with binary strings.

You may therefore have some luck when using a binary string instead:

WHERE response REGEXP BINARY '^[a-z]'

Reasonably silly fiddle for demonstration: http://sqlfiddle.com/#!9/7eade/3

EDIT: As Ray says in the comments, you should probably use [[:lower:]] instead in order to work across all collations, e.g.

WHERE response REGEXP BINARY '^[[:lower:]]'