mysql chokes on certain characters and mssql not

Question

What's a good way to filter string data so that we don't get a fatal error at an insert operation like this?

ERROR: SQL FAILS: Incorrect string value: '\x92s]' for column 'looseterm' at row 1

Failing SQL is INSERT INTO `looseterms` (`looseterm`) VALUES ('[loose_terms:indonesia’s]')

Does anyone know what the heck '\x92s]' is and what is it so dear to mysql to choke on it? Ms-Sql got no problems with it.

Answer 1

Set your connection character set to that in which your literals are encoded. For example, for UTF-8:

SET NAMES 'utf8';

If different literals are in different encodings, you can specify the character set of those not in the connection character set with an introducer:

INSERT INTO ... (_utf8'[loose_terms:...');