How to search for the exact string value in MySQL?

Question

I got this code:

select * from locality WHERE name ="ISTASYON"

This works perfectly with mySQL. The problem comes, when I try to execute but result is incorrect

Result code:

28BAF9346A41E4E4E0501AAC4524363B    0       iSTASYON
402881a4523b52d201523b6c2afb4166    0       İSTASYON
402881a4523b52d201523b6c38b7417c    0       İSTASYON
402881a4523b52d201523baa9faf0092    0       İSTASYON
402881a4523b52d201523baab059009f    0       İSTASYON
402881a4523b52d201523baad01a00b7    0       İSTASYON
58441bc4c054447ebe1cddbfeef958b5    0       ISTASYON
fa7fb88d1d4c41feb497b08f42066c82    1   2016-04-19 09:53:41.000000  İSTASYON

My problem is that results contain ISTASYON, İSTASYON, ıSTASYON, iSTASYON but i wanna only ISTASYON

How to solve this problem ?

Possible duplicate of [How can I make SQL case sensitive string comparison on MySQL?](http://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql) — Shadow, Sep 30 '16 at 12:52
The 2nd answer with the most upvotes in the duplicate question is the simplest solution — Shadow, Sep 30 '16 at 12:54

Eugene Yarmash · Accepted Answer · 2016-09-30T13:15:14.733

2

You can use the COLLATE or BINARY operator to force binary comparison:

SELECT * FROM locality WHERE name COLLATE utf8_bin = "ISTASYON"

or

SELECT * FROM locality WHERE BINARY name = "ISTASYON"

If you want the column always to be treated in this fashion, declare it with a binary collation.
See the docs for more info.

edited Sep 30 '16 at 13:15

answered Sep 30 '16 at 12:51

Eugene Yarmash

142,882
41
325
378

score 1 · Answer 2 · edited Dec 26 '16 at 09:41

1

You can use a select query like this, I also use and it's helpful.

Select * from `users` where username COLLATE latin1_general_cs LIKE '%$email%'

edited Dec 26 '16 at 09:41

user247702

23,641
15
110
157

answered Dec 26 '16 at 09:27

Rahul Dhiman

19
6

score 0 · Answer 3 · answered Sep 30 '16 at 12:54

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'i%', you get all column values that start with I or i.To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation.

col_name COLLATE latin1_general_cs LIKE 'i%'
col_name LIKE 'i%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'i%'
col_name LIKE 'i%' COLLATE latin1_bin

score 0 · Answer 4 · answered Sep 30 '16 at 12:55

0

select * from locality WHERE BINARY name ="ISTASYON"

or

select * from locality WHERE name = BINARY "ISTASYON"

answered Sep 30 '16 at 12:55

Khalid Mahmood

31
1
3

score 0 · Answer 5 · answered Oct 01 '16 at 05:36

Plan A: This will be case-sensitive and accent sensitive:

It would be more efficient (after setup) to have the column be utf8_bin (or utf8mb4_bin. latin1 (and latin1_bin) will not suffice because of the Turkish characters.

The setup is

ALTER TABLE tbl MODIFY COLUMN ... COLLATION utf8_bin ...;

Caution: If you also want to do case folding and/or accent stripping with the same column(s) in other situations, this will hurt those situations.

Plan B: This addresses the dotless ı and a few other Turkish idiosyncrasies.

COLLATION utf8_turkish_ci (or utf8mb4_turkish_ci) treats these equal: I=ı, then treats the following as different and later, but all equal: i=Ì=Í=Î=Ï=ì=í=î=ï=Ī=ī=Į=į=İ. Ü=ü come after U and before V. Similarly Ğ=ğ come after G and before H. See utf8 collations for more details.

How to search for the exact string value in MySQL?

5 Answers5