2

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

glglgl
  • 89,107
  • 13
  • 149
  • 217
MUFC
  • 1,943
  • 3
  • 14
  • 13

2 Answers2

4
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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 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
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.

alinoz
  • 2,822
  • 22
  • 38