Mysql – how does one quote unicode characters in thesql prompt or in SQL in general

MySQLsqlunicodeutf-8

I have a weird unicode char in my mysql database

the value looks like this

card issuer bank didn<U+0092>t approve your payment

so what should be an apostrophe is a weird unicode char, presumably from windows

I want to replace it, but don't know how to quote it in SQL

tried the following, doesnt work:

replace(text,cast(0x0092 as character set utf8),'x')

0x0092

0x000x92

'\U+0092'

U'0x0092'

and tons of other combinations, none of them work

any ideas?

Best Answer

This page seems to be very close to what you're looking for, although the specific values are different; 0x0092 is decimal 146, the Windows code for right smartquote. That's the equivalent of 0xe28099 in UTF-8, as you'll see in the link :)

In summary:

This code should operate with both the Windows-1252 charset, and also UTF-8, an encoding with an extended character set that has made it the preferred encoding for email and websites.

UPDATE `t` SET `c` = REPLACE(`c`, 0xE28098, "'");
UPDATE `t` SET `c` = REPLACE(`c`, 0xE28099, "'");
UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809C, '"');
UPDATE `t` SET `c` = REPLACE(`c`, 0xE2809D, '"');

...and so on.

Related Topic