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 lowercase row with abcde, but not the rows with capitals. I cannot seem to find an easy way to do this. Am I missing something stupid?

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.

Answer 2

Edit: I've misread OP and this is solution for the opposite case where MySQL is in SENSITIVE collation and you need to compare string in INSENSITIVE way.

MySQL 5.x

You can workaround it using LOWER() function, too.

SELECT * 
FROM datatbl 
WHERE LOWER(strfield) RLIKE '[a-z]*';

MySQL 8+

If you are running MySQL 8+, you can also use case-insensitive switch in REGEXP_LIKE() function.

SELECT * 
FROM datatbl 
WHERE REGEXP_LIKE(strfield, '[a-z]*', 'i');

Answer 3

For case-sensitive regex you can use REGEXP_LIKE() with match type c like this:

SELECT * FROM `table` WHERE REGEXP_LIKE(`column`, 'value', 'c');