CODEWITHSUNDEEP
mysqlsqlencoding
MUFC
MUFC

Reputation: 2013

How to check if a row in a table has muti-byte characters in it in a MySQL database?

I have a table which has column of descr which stores string values. Some of the values in descr has multi-byte characters in it and I want to know all those rows so I can remove those characters. How can I query the tables using MySQL functions to determine which rows have multi-byte characters. I am using MySQL version 5.1

Upvotes: 2

Views: 991

Answers (2)

alinoz
alinoz

Reputation: 2822

have you tried the collation and CHARSET functions on your descr column?

You can find the description of this functions here: http://dev.mysql.com/doc/refman/5.1/en/information-functions.html

I think for your need it fits better the COERCIBILITY function. You can do something like:

 select COERCIBILITY(descr, COLLATE utf8) from myTable;

and if this function returns 0 then you must edit the line.

Upvotes: 1

Marc B
Marc B

Reputation: 360782

SELECT ...
FROM yourtable
WHERE CHAR_LENGTH(descr) <> LENGTH(descr)

char_length is multi-byte aware and returns the actual character count. Length() is a pure byte-count, so a 3-byte char returns 3.

Upvotes: 4

Related Questions