How to conduct an Accent Sensitive search in MySql

Question

I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:

abad
abád

I am using a query that looks like this:

SELECT *  FROM `words` WHERE `word` = 'abád'

The query result gives both words:

abad
abád

Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return

abád

I have also tried this query:

SELECT *  FROM `words` WHERE BINARY `word` = 'abád'

It gives me no results. Thank you for the help.

score 99 · Accepted Answer · edited Jun 05 '17 at 18:01

99

If your searches on that field are always going to be accent-sensitive, then declare the collation of the field as utf8_bin (that'll compare for equality the utf8-encoded bytes) or use a language specific collation that distinguish between the accented and un-accented characters.

col_name varchar(10) collate utf8_bin

If searches are normally accent-insensitive, but you want to make an exception for this search, try;

WHERE col_name = 'abád' collate utf8_bin

edited Jun 05 '17 at 18:01

Shadow

32,277
10
49
61

answered Feb 01 '09 at 17:01

1

Thanks for your suggestion, but I'm getting: "COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'". – OMA Jul 24 '12 at 11:12
1

BTW, my character set is really "utf8_general_ci", so I don't know why it says the character set is "latin1". – OMA Jul 24 '12 at 11:18
3

@OMA look for the character set and collate for DB, Table and column, all should be the same in order to work properly – linuxatico Jan 08 '14 at 09:11
4

this also works using the LIKE wildcard operator by `... WHERE CONVERT(field_name USING latin1) like '%á%' COLLATE utf8_bin` – Abel Callejo May 03 '15 at 16:00
`COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'` – User Apr 10 '16 at 19:48
12

@User Try using `collate utf8mb4_bin` instead. – Dan Breen Jan 26 '17 at 15:33
@DanBreen Ya, except that has issues on Windows. – User Jan 27 '17 at 05:24
Is there any collation (as of now) which facilitates the CaseInsensitive and AccentSensitive? – sam Aug 25 '17 at 17:12
Unrecognized keyword. (near "collate" at position 44) – Phil Jul 11 '18 at 05:29

David · Answer 2 · 2017-05-18T10:33:37.687

16

In my version (MySql 5.0), there is not available any utf8 charset collate for case insensitive, accent sensitive searches. The only accent sensitive collate for utf8 is utf8_bin. However it is also case sensitive.

My work around has been to use something like this:

SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin

edited May 18 '17 at 10:33

answered Aug 23 '10 at 16:54

David

2,682
29
15

4

`#1253 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4' ` – User Apr 10 '16 at 19:49
5

Then you should use utf8mb4_bin. – David Apr 11 '16 at 10:33
1

if you want to make it both-side-case-insensitive, don't forget to also `LOWER('the_searched_value')` so you will get `LOWER(column) = LOWER('aBád')` – jave.web Apr 05 '17 at 17:53
I also had to use utf8mb4_bin... this was for an "inline query" in phpMyAdmin. Wonder where and why these CHARACTER SETs are set...? – mike rodent May 15 '17 at 16:03

score 3 · Answer 3 · answered Dec 05 '17 at 02:39

Accepted answer is good, but beware that you may have to use COLLATE utf8mb4_bin instead!

WHERE col_name = 'abád' collate utf8mb4_bin

Above fixes errors like:

MySQL said: Documentation 1253 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

score 3 · Answer 4 · answered Feb 24 '11 at 20:57

3

The MySQL bug, for future reference, is http://bugs.mysql.com/bug.php?id=19567.

answered Feb 24 '11 at 20:57

colan

2,688
1
18
17

score 2 · Answer 5 · edited Aug 14 '20 at 17:18

2

Check to see if the database table collation type end with "_ci", This stands for case insensitive...

Change it to collation the same or nearest name without the "_ci" ...

For example... change "utf8_general_ci" to "utf8_bin" Mke

edited Aug 14 '20 at 17:18

HoldOffHunger

15,349
8
79
115

answered Sep 17 '13 at 03:50

Mike

99
1
3

score 1 · Answer 6 · edited Nov 01 '21 at 12:33

1

I was getting the same error.

I've changed the collation of my table to utf8_bin (through phpMyAdmin) and the problem was solved.

edited Nov 01 '21 at 12:33

Dharman

26,923
21
73
125

answered May 01 '13 at 17:40

Silvio Delgado

6,628
3
17
21

score 0 · Answer 7 · answered Aug 26 '09 at 09:56

Well, you just described what utf8_general_ci collation is all about (a, á, à, â, ä, å all equals to a in comparison).

There have also been changes in MySQL server 5.1 in regards to utf8_general_ci and utf8_unicode_ci so it's server version dependent too. Better check the docs.

So, If it's MySQL server 5.0 I'd go for utf8_unicode_ci instead of utf8_general_ci which is obviously wrong for your use-case.

score 0 · Answer 8 · answered Apr 16 '18 at 09:30

That works for me for an accent insensitive and case insensitive search in MySql server 5.1 in a database in utf8_general_ci, where column is a LONGBLOB.

select * from words where '%word%' LIKE column collate utf8_unicode_ci

with

select * from words where'%word%' LIKE column collate utf8_general_ci

the result is case sensitive but not accent sensitive.

score 0 · Answer 9 · answered Feb 01 '09 at 13:48

0

SELECT *  FROM `words` WHERE column = 'abád' collate latin1_General_CS

(or your collation including cs)

answered Feb 01 '09 at 13:48

cjk

44,524
9
79
111

score 0 · Answer 10 · answered Feb 01 '09 at 14:08

You can try searching for the hex variable of the character, HEX() within mysql and use a similar function within your programming language and match these. This worked well for me when i was doing a listing where a person could select the first letter of a person.

How to conduct an Accent Sensitive search in MySql

10 Answers10

Linked

Related