Thursday, April 18, 2013

I imported data from an external source into MySQL and found few strange characters.

How do we find out the records which have characters outside of the charset (ASCII in my case)?


Solution:-

MySQL provides a nice character set management which can be useful for such scenarios.

SELECT anything

FROM table_name
WHERE column_to_check <> CONVERT(column_to_check USING charset)

CONVERT(column_to_check USING charset) would convert the nonconvertible character into their respective replacements. Hence, the converted and unconverted text would be unequal.


For example,

SELECT eName
FROM employee
WHERE eName <> CONVERT(eName USING ASCII)

No comments: