selecting words based on occurrance of letters, etc...

[.josh]

Okay I'm fiddling around with this list of words and I'm just trying to figure out what kind of neat things I can do with mysql, as far as selecting words.  So I thought I'd try selecting all words in my list, based on for example, how many s's are in it.  For example, I want to select all words that contain exactly 4 s's in it

Now, after some researching, I came up with the following:

[code]
SELECT * FROM word_list WHERE length(word) - length(replace(word, 's', '')) = 4
[/code]

This works great! However, I am running into some problems when trying to do the same thing, only selecting words with (for instance) [i]more than[/i] 4 s's in it, or [i]less than[/i] 4 s's in it.  It returns the results just fine, but I can't seem to wrap my head around how to order it by occurance. 

I am thinking I have to somehow do something like

[code]
SELECT count(length(word) - length(replace(word,'s','')) as d, * FROM word_list WHERE length(word) - length(replace(word, 's', '')) = 4 order by d
[/code]

but obviously that doesn't work... Am I even remotely on the right track here?  I know how to manipulate a list of words just fine with php. I can pull them all out and into an array and then sort it out just fine using some regex and stuff...but dealing with 58,000 words.. i'm thinking I should make mysql do the work, if I can somehow get it to work, lol..

[artacus]

Your query doesn't work because you selected an "*" AFTER defining a field.  Either put it before "d" or use word_list.*

[.josh]

thanks! I also figured out from that that i needed to remove the COUNT. Final query looks like this (example):

[code]
SELECT length( word ) - length( replace( word, 's', '' ) ) AS d, word_list. *
FROM word_list
WHERE length( word ) - length( replace( word, 's', '' ) ) < 4
ORDER BY d DESC
[/code]