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.

Answer 1

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

(or your collation including cs)

Answer 2

I gave this a try:

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

But I receive this error: #1253 - COLLATION 'latin1_General_CS' is not valid for CHARACTER SET 'utf8'

So then I tried:

SELECT *  FROM `words` WHERE column = 'abád' COLLATE utf8_unicode_ci_CS

It gave this error: #1273 - Unknown collation: 'utf8_unicode_ci_CS'. I also tried this without the "_CS", but the search was not accent sensitive.

Answer 3

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.

Answer 4

Will adding the HEX command on the column negate the benefits of the Index? This queries will be run frequently.

Answer 5

_ci in a collation name=case insensitive

If your searches on that field are always going to be case-sensitive, then declare the collation of the field as utf8_bin... that'll compare for equality the utf8-encoded bytes.

col_name varchar(10) collate utf8_bin

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

WHERE col_name = 'abád' collate utf8_bin.

Answer 6

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.

Answer 7

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

Answer 8

thank you david for your solution, you saved me ;) I only used COLLATE utf8_bin and worked perfectly. Bye