CODEWITHSUNDEEP
mysqlsql
Francis Lewis
Francis Lewis

Reputation: 8990

Select results with a maximum string length

I have a table that looks like this:

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+

Pretty simple table. I'm trying to find a way to only select results with a maximum length of 8 characters for a couple of reasons:

1) The HTML table that was created for the record set is too narrow to work with anything greater than 8 characters.

2) We can't have the names truncated when displaying them.

I was looking through the MySQL manual and can't find any solid results. I'm going to try using char_length in the where clause and see what I can come up with and will post back what I find, but I'm hoping somebody has done this before.

Upvotes: 3

Views: 1443

Answers (1)

John Woo
John Woo

Reputation: 263913

You need to use CHAR_LENGTH to get the length of the characters. Try:

SELECT *
FROM tableName
WHERE CHAR_LENGTH(name) <= 8       -- selects 8 characters or below
-- WHERE CHAR_LENGTH(name) = 8     -- selects exactly 8 characters

Additional Info:

LENGTH() returns the length of the string measured in bytes.
CHAR_LENGTH() returns the length of the string measured in characters.

select length(_utf8 '€') as ByteCount, char_length(_utf8 '€') LengthCount

--  Result
--  ============+==============
--  ByteCount   +   LengthCount
--  ============+==============
--         3    +           1
--  ============+==============

Upvotes: 5

Related Questions