13

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
frosty
  • 2,559
  • 8
  • 37
  • 73
  • 3
    As per the [docs](http://dev.mysql.com/doc/refman/5.7/en/regexp.html): `REGEXP is not case sensitive, except when used with binary strings. ` – Marc B Jun 16 '16 at 20:41

4 Answers4

11

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:]]'
slugonamission
  • 9,562
  • 1
  • 34
  • 41
  • Don't you think `[[:lower:]]` is more general than `[a-z]` across character sets and collations ? – Ray Jun 16 '16 at 20:55
  • Characters that's not English alphabets? – frosty Jun 16 '16 at 21:11
  • @frosty Yep. Might not be a concern for your particular use case now, but why not use the the built-in freebie and future protect your query? – Ray Jun 17 '16 at 01:02
2

I would use mysql's Character Class Name to match in conjunction with REGEXP BINARY :

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

I don't know if [a-z] makes sense in every character set and collation, where as the character class name [:lower:] will always match all lower case alpha characters.

Ray
  • 40,256
  • 21
  • 101
  • 138
1
SELECT *
FROM allData
WHERE LOWER(LEFT(response, 1)) <> LEFT(response, 1)
LIMIT 0 , 30

...however, this may be limited by your MySQL character collation

Community
  • 1
  • 1
Tony Chiboucas
  • 5,505
  • 1
  • 29
  • 37
0
select * from alldata where lower(left(response,1)) COLLATE Latin1_General_CS_AS =left(response,1) and response is not null and response<>''
Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25