mysql sort question

Question

Hello, i have a database and I want to sort the a column according to the number of words included in each field.

Ex:

a b (has 2 words)
a b c d e (has 5 words)
a b c (has 3 words)

And i want 'a b c d e' to be the first in sort result, and 'a b c' the second. .. etc

Do you have a solution fot it?

Edit: Thanks guys for quicly answers! All answers was good but meanwhile i solved using same sollutions found here mysql-substr-count

Answer 1

You can sort by the number of spaces:

order by length(field) - length(replace(field, ' ', '')) desc

Answer 2

this should do the trick, but maybe there's a better (faster) solution:

SELECT *
FROM table
ORDER BY LENGTH(column) - LENGTH(REPLACE(column, ' ', ''))

Answer 3

Hum - here's an example on how to count words in one column. Maybe it can be extended like this

SELECT 
    SUM( LENGTH(`name`) - LENGTH(REPLACE(`name`, ' ', ''))+1) AS `num_words`
FROM `table`
ORDER BY `num_words`

(not tested - sorry.)

Answer 4

You are most likely wrong with your database structure. if you ask such a question.
Such a numbers ought to be taken from the database structure, not field contents.
As immediate result of such a bad design is terrible performance of all the queries from the answers above.