Case sensitive RLIKE

Question

Consider a table datatbl like this:

+----------+
| strfield |
+----------+
|    abcde |
|    fgHIJ |
|    KLmno |
+----------+

I want to write a query something like this:

select * from datatbl where strfield rlike '[a-z]*';

As in a non-SQL regex, I'd like to return the row w/ abcde, but not the rows w/ capitals. I cannot seem to find an easy way to do this. Am I missing something stupid?

Thanks, Joe

Answer 1

The MySQL REGEXP/RLIKE sucks for this - you need to cast the data as BINARY for case sensitive searching:

SELECT * 
  FROM datatbl 
 WHERE CAST(strfield  AS BINARY) rlike '[a-z]*';

You'll find this raised in the comments for the REGEXP/RLIKE documentation.