mysql query based on length of string in a column

Question

I am trying to query a table in mysql based on the length of a string in a specific column. I know mysql has a function called LENGTH(), but that returns the length of the string. I want to be able to pull data based on the result of the LENGTH() function.

Example:

SELECT * table WHERE LENGTH(word) = 6

of course that does not work. I read through http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function%5Flength but could not find anything to help me.

yes I could make something in PhP to accomplish this, but I would like to do it at the query level.

Any help?

Joe Stefanelli · Accepted Answer · 2012-01-19 22:54:58Z

4

Try:

SELECT *
    FROM table
    WHERE LENGTH(RTRIM(word)) = 6

answered Jan 19, 2012 at 22:54

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ike Walker Over a year ago

I would recommend using the CHAR_LENGTH() function instead of LENGTH(), that way you're counting characters instead of bytes.

user1130861 Over a year ago

worked perfect, ultimatly I am making a query statement that will pull results based on "String Length", "Possible Characters", and not "Including Characters" in words. I have the first 2 so far

Vyktor · Accepted Answer · 2012-01-19 22:56:13Z

0

I believe you wanted to use query SELECT * FROM tableName WHERE LENGTH(word) = 6; (assuming that the word is name of column in tableName).

This is very unfortunate solution on large tables, you should create new column and use UPDATE tableName SET wordLength = LENGTH( word).

answered Jan 19, 2012 at 22:56

Vyktor

21.1k6 gold badges69 silver badges98 bronze badges

Collectives™ on Stack Overflow

mysql query based on length of string in a column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related