Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
thanks, not exactly the answer to my question, but very useful nevertheless –  Aleksandar Ivanisevic Sep 23 '09 at 11:13

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)

share|improve this answer
    
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. –  Aleksandar Ivanisevic Sep 21 '09 at 13:06
up vote 0 down vote accepted

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

share|improve this answer
    
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 '12 at 12:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.