MySQL: ss and ß in LIKE

Question

Is there any solution that MySQL can automatically handle ss and ß in LIKE statements? In a statement with equals = it works, but we have a search text field so the term is not equal to the search data in the database.

Only to change the collation of the database has not solved the problem, because it will only work for equal statements (=).

Possible duplicate of [What is the best MySQL collation for German language](https://stackoverflow.com/questions/5526169/what-is-the-best-mysql-collation-for-german-language) — Nico Van Belle, Jul 13 '17 at 13:50
@NicoVanBelle: Please see the question update; it's not a duplicate because the change of collation seems insufficient to solve the problem. — 9000, Jul 13 '17 at 15:46

score 3 · Accepted Answer · answered Jul 13 '17 at 13:52

3

You could use REPLACE() on the field you're comparing. Something like this:

WHERE REPLACE(YourField, 'ß', 'ss') LIKE '%ss'

answered Jul 13 '17 at 13:52

Aaron Dietz

10,137
1
14
26

Will it prevent the use of an index by `YourField`, if it exists? – 9000 Jul 13 '17 at 14:11
1

@9000 Yep it will. I still prefer it to the German collation option, since I think having umlaut comparisons being equal, ex. `u = ü`, is a huge problem. But if there are significant slowdowns, that could be handled in many ways, such as creating another indexed column with the replaced character field. – Aaron Dietz Jul 13 '17 at 14:29
@aaron-diez Too bad MySQL does not support functional indexes. When [this option](https://stackoverflow.com/a/35380963/223424) is not sufficient, probably a proper full-text indexing server is the right answer. – 9000 Jul 13 '17 at 14:33
This will become horrible slow if you have a big dataset. – jor May 05 '21 at 15:51

MySQL: ss and ß in LIKE

1 Answers1