0

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?

3 Answers 3

2

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.

1
  • thanks, not exactly the answer to my question, but very useful nevertheless Sep 23, 2009 at 11:13
0

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)

1
  • I would if I had only this one instance. This (and other) fancy characters are scattered in thousands of records. If I only knew how to write a unicode literal, i could do select replace() over all entries. Sep 21, 2009 at 13:06
0

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

1
  • 1
    This is because of UTF-8 encoding. Linux displays it as unicode character U+0092, but the actual binary encoding of the character is 0xc292. See en.m.wikipedia.org/wiki/Utf8
    – Luke H
    Aug 29, 2012 at 12:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .