17

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?

Lee Goddard
  • 10,680
  • 4
  • 46
  • 63
Joe Mastey
  • 26,809
  • 13
  • 80
  • 104

3 Answers3

24

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.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 2
    Just for future reference, to match the whole string as OP seemed to want, the regex would need to be- `rlike '^[a-z]*$'` – Kip Mar 30 '17 at 21:04
1

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');
CraZ
  • 1,669
  • 15
  • 24
1

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

SELECT * FROM `table` WHERE REGEXP_LIKE(`column`, 'value', 'c');
But those new buttons though..
  • 21,377
  • 10
  • 81
  • 108
  • 1
    [Link to documentation](https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-like) – Rorrim Jul 22 '22 at 16:06