Incorrect string value

turns out the issue was the charset in db was set to latin_sweedish

i changed it to utf8_general_ci and that sorted it.

there was one additional twist to this which i solved by looking here MySQL Bugs: #33475: Incorrect string value when inserting unicode codepoint \xC2\x92

the key part being at the bottom "

[9 Dec 2011 0:07] Arkadiy Kulev

This problem solves easily. Don't forget to not only set the database, table and collation to utf8, BUT THE COLUMNS ALSO!

That's what cause the problem for me. I created the table in latin, then switch to utf8, but forgot to also change the columns."

that caught me out but got there in the end.

HTH anyone else who has this.

FYR I am hoping that this How to turn this Java into something I can use in CF? will eventually provide anyone who's interested with a way to remove special characters from a string before inserting in the db

The Hex value 0xE2 0x80 0x8B is the representation in UTF-8 of the 'zero-width space' character. You may remove it from the string - for example, by replacing it with a blank space - before the string enters the query.

Assuming that chr(8203) represents the zero-width character, you could replace it as follows:

<cfset myString = replace(myString,chr(8203),"","all")>