MySQL: Querying for unicode entities

Solution 1:

I have absolutely no idea why, but triple escaping helps!

Well, that's only double-escaping, but yes it works and here's why: in MySQL, there is a second layer of escaping involved when you use the LIKE operator.

services LIKE '%L\\\\u00e4mm\\\\u00f6n%'

parsing that MySQL string literal gives you a comparison with the LIKE-query %L\\u00e4mm\\u00f6n%. Because MySQL treats \ in a LIKE query as an escape, that will actually match the literal string containing L\u00e4mm\u00f6n.

The reason for this is so that you can match strings against a query expression that contains a literal % or _ character. For example if I want to search a column for the literal string 100%, I can match it against 100\% (written in a query as '100\\%') and make sure I'm really getting one hundred percent and just not any string starting with a hundred.

It's unfortunate that MySQL uses backslash for both its LIKE query escaping and its string literal escaping, especially given that you're probably writing in an enclosing programming language that also uses them, ending up with actual triple-encoding, which looks like "services LIKE '%L\\\\\\\\u00e4mm\\\\\\\\u00f6n%'" - argh!

It's doubly unfortunate given that this behaviour is not ANSI SQL conformant, and won't work in any other database. ANSI SQL says that there is no escape character in LIKE queries by default, so if you want to match a literal % or _ you have to opt in by nominating an escape character of your own, eg.:

something LIKE '100=%' ESCAPE '='

For cross-database compatibility, it is best always to use the LIKE...ESCAPE form, and pick something other than the horrible backslash! (Aside - MySQL's backslashes for SQL string literal escaping aren't ANSI conformant either! But you can turn that misbehaviour off with the NO_BACKSLASH_ESCAPES sql_mode setting.)

Probably a better idea would be to break services out into a second table rather than squashing them into a single string column - ie. put your schema in First Normal Form. Then you could get a simple lookup of individual values rather than having to do a slow full-table-scan substring-match.

Solution 2:

The backslash is a meta character, MySQL understand it this way : "remove the next character and don't parse it as a meta-character".

So you need to escape the backslash :

SELECT * FROM table WHERE (services LIKE '%L\\u00e4%')

Now, MySQL will replace "\\" by "\" (the first backslash is a meta character used to escape the second one)

Solution 3:

I have absolutely no idea why, but triple escaping helps!

SELECT id, uid, company_name, services, logo
FROM rekisteroeidy_toimijaks
    WHERE (
    services LIKE  '%L\\\\u00e4mm\\\\u00f6n%'
)
ORDER BY company_name ASC 
LIMIT 0 , 30