4

MySQL uses collations to do string comparison because some characters should match

Exemple:

SELECT 'é' = 'e' COLLATE utf8_unicode_ci;
SELECT 'oe' = 'œ' COLLATE utf8_unicode_ci;

both return true

Now, how can I do the same with quotes (') vs apostrophes (’)

This is not the same character, the proper character to use when writing “it’s” or “l’oiseau” (in french) are both the apostrophe.

The fact is that neither utf8_general_ci or utf8_unicode_ci collate them.

The easy solution is to store everything in quotes and do a replace of all the apostrophes when a user does a search, but it’s wrong.

The real solution would be to create a custom collation based on utf8_unicode_ci and mark both as equivalent, but that requires to edit XML config files and to restart the database, which isn’t always possible.

How would you do it?

cslavoie
  • 216
  • 1
  • 6
  • so you want a "real solution" other than the one you already mentioned? ;-) My suggestion would have been to create a custom collation (assuming, of course that you've already scoured the mysql docs and other 3rd party sources for an existing collation that already does what you want) – Lee Dec 08 '10 at 04:52

1 Answers1

1

A custom collation seems to be the most appropriate, but if that is not possible, perhaps you could tailor your searches to use regular expressions. It's not exactly ideal, but may be of use in some situations. At least it allows you to store data in the correct format (without having to replace quotes), and just do the replacements on the search query itself:

INSERT INTO mytable VALUES
(1, 'Though this be madness, yet there is method in ''t'),
(2, 'Though this be madness, yet there is method in ’t'),
(3, 'There ’s daggers in men’s smiles'),
(4, 'There ’s daggers in men''s smiles');

SELECT * FROM mytable WHERE data REGEXP 'There [\'’]+s daggers in men[\'’]+s smiles';

+----+--------------------------------------+
| id | data                                 |
+----+--------------------------------------+
|  3 | There ’s daggers in men’s smiles     |
|  4 | There ’s daggers in men's smiles     |
+----+--------------------------------------+

SELECT * FROM mytable WHERE data REGEXP 'Though this be madness, yet there is method in [\'’]+t';

+----+-----------------------------------------------------+
| id | data                                                |
+----+-----------------------------------------------------+
|  1 | Though this be madness, yet there is method in 't   |
|  2 | Though this be madness, yet there is method in ’t   |
+----+-----------------------------------------------------+
Mike
  • 21,301
  • 2
  • 42
  • 65