MySQL Question

Selecting by a string length

[panic]

Since there's no forum where I can ask about MySQL, I'm guessing the next best one would be here.

Is there any way to select a MySQL row by the length of a string?

For example, let's say I have a table with three fields : ID, Name, and Comment. Essentially what I want to do is select rows where the length of Comment is greater than 50 characters.

How do I go about doing this?

[rubenski]

I think you would have to store the string length of "comment" in a separate field. Correct me if i am wrong.

Another way is to select all comments and use PHP to look for the string length of every comment. But of course that is ad hoc and less efficient.

[panic]

I think you would have to store the string length of "comment" in a separate field.

Sensible answer.

Another way is to select all comments and use PHP to look for the string length of every comment. But of course that is ad hoc and less efficient.

It would slow load time immensely, and would probably put a hurting on my MySQL daemon :\

Another solution I came up with upon reading your response was to create another field in the table for comment length, and stick the result of strlen($comment) in the comment field length.

However, selecting by length is a solution I only want to use temporarily. Is there a way where all I have to do is edit the query?

[killroy]

ERM, Maybe I'm silly, but why not just:

SELECT * FROM tablename WHERE LENGTH(comment)>50;

?
Perhaps you want to check out: MySQL Manual: String Functions: LENGTH() [mysql.com]

SN

[panic]

I searched for length in the MySQL documentation and got the following [mysql.com ]

So uh... spare the sarcasm.

[rubenski]

Haha, he is right though. I didn't know myself you can put length() in the mysql query. :-)

[httpwebwitch]

Don't feel bad for that forehead-slapper. The MySQL site really is horribly organized, especially to people who are used to the sensible online manual at PHP.net. It is nearly impossible to find these almost-hidden functions, and the documentation is about as readable as upside-down IKEA instructions. Maybe they are getting royalties from sales of printed manuals that explain and list all these MySQL tricks?

[panic]

I doubt it.

[jatar_k]

it is difficult httpwebwitch, I agree

It takes a lot more getting used to than the php.net site but I would say that the php.net site is the best site I have ever seen for a language. Once you get used to the mysql site it works quite well. It just isn't overly forgiving for people trying to learn sql. If you know roughly what you are looking for it is usually a click or two away.

[coopster]

A little trick I use -- download the PDF version of the MySQL Manual and search using that software as opposed to web-based search engines. This comes in really handy when you want to search only certain chapters of the manual, etc.