Home / server / Questions / 67272
Accepted
Aleksandar Ivanisevic
Asked: 2009-09-22 02:41:07 +0800 CST

how does one quote unicode characters in mysql prompt or in SQL in general?

  • 772

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?

mysql

3 Answers

  1. 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.

    • 2

  2. Why not just brute force it to what you want?

    update set = "card issuer bank didn't approve your payment" where =;

    If there are many of these with the same issue, maybe trap it on the insert in the code?

    If this is still not possible, try to convert() function on the whole value of the column.

    convert( using utf8)

    • 0
  3. Best Answer

    at the and I've ran the select result through the hex editor to figure out exactly whats coming out, so it turns out that is actually 0xc292, like described here.

    Why would linux display this as U+0092 is beyond me

    • 0