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

Question

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

score 3 · Accepted Answer · answered Sep 27 '11 at 16:19

3

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.

answered Sep 27 '11 at 16:19

Marc B

348,685
41
398
480

This worked perfectly. Thanks a lot for your help. Now I need to figure out how to display them in Mysql since the default charecter set on Mysql is Latin1 – MUFC Sep 27 '11 at 16:23

alinoz · Answer 2 · 2011-09-27T16:27:23.817

1

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.

edited Sep 27 '11 at 16:27

answered Sep 27 '11 at 16:15

alinoz

2,810
21
37

No I have not and I have no clue how to use them ? – MUFC Sep 27 '11 at 16:18
select charset(descr) from myTable; – alinoz Sep 27 '11 at 16:20

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

2 Answers2