Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mysql - Case sensitive RLIKE

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...