How to filter rows based on the character length of a field

Question

I have a table with column "id" and "description". Need to find those rows where description has more than 100 characters in it?

EDIT: now got the Above question.. But is there any way to get the count of number of characters ?

Answer 1

Use the char_length() string function.

SELECT * FROM table WHERE char_length(description) > 100

Check out more information here http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_char-length

Answer 2

Is this what you're looking for?

select * from table
where char_length(description) > 100

Just a comment about length(). Actually, I'm lazy and I'm copy pasting it from the official documentation:

Length(str): Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

So, length() is not appropriate to solve this issue. User char_length() instead.

Answer 3

Although you should really create a separate question for the edit you made post-answer, here's how you would get the count of the number of characters where the description is longer than 100 characters:

SELECT char_length(description) FROM table WHERE char_length(description) > 100