CODEWITHSUNDEEP
mysqlutf-8collationcase-insensitive
Simon
Simon

Reputation: 12488

Looking for case insensitive MySQL collation where "a" != "ä"

I'm looking for a MySQL collation for UTF8 which is case insensitive and distinguishes between "a" and "ä" (or more generally, between umlauted / accented characters and their "pure" form). utf8_general_ci does the former, utf8_bin the latter, bot none does both. If there is no such collation, what can I do to get as close as possible in a WHERE clause?

Upvotes: 4

Views: 3399

Answers (4)

BjeMTj
BjeMTj

Reputation: 11

I checked utf8_bin like this

CREATE TABLE tmp2 (utf8_bin VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
INSERT INTO tmp2 VALUES ('nói');

select * from tmp2 where utf8_bin='noi';

Upvotes: 0

cweiske
cweiske

Reputation: 31088

It works fine here with utf8_german2_ci as collation:

SELECT * FROM tablename WHERE fieldname LIKE "würz%" COLLATE utf8_german2_ci

Upvotes: 0

Christian Davén
Christian Davén

Reputation: 18117

You could try utf8_swedish_ci, it's both case insensitive and distinguishes between a and ä (but treats e.g. ü like y).

Collations are language-dependent, and it seems German doesn't have its own collation in MySQL. (I had a look at your profile, which says you're German.)

Upvotes: -1

Andy West
Andy West

Reputation: 12499

My recommendation would be to use utf8_bin and in your WHERE clause, force both sides of your comparison to upper or lower case.

Upvotes: 7

Related Questions