Count words in column with SQL

Question

id | words                            | other
1  | lorem ipsum                      | dsf
2  | Lorem Ipsum is simply dummy text | sads
3  | simply dummy text                | sdf
4  | Lorem Ipsum  text                | sdfsdf
5  | simply dummy Lorem               | asddasd

I do SELECT * FROM words . How can i count how many words is in this answer of query? In this example should be 17. I use PHP and Doctrine.

If you retrieve the data to PHP from the resultset, then use PHP's str_word_count() function; if you want to do the wordcount in MySQL Itself, look at this answer - http://stackoverflow.com/questions/748276/using-sql-to-determine-word-count-stats-of-a-text-field — Mark Baker, Feb 03 '12 at 11:37

score 5 · Accepted Answer · answered Feb 03 '12 at 11:41

5

SELECT LENGTH(words) - LENGTH(REPLACE(words, ' ', '')) + 1 AS words_count
FROM table_name

answered Feb 03 '12 at 11:41

Alex Pliutau

21,392
27
113
143

1

Not a good solution - it works only with clean data, too many problems if there are multiple spaces between words, or leading/trailing spaces – Mark Baker Feb 03 '12 at 11:45

score 0 · Answer 2 · edited Oct 30 '12 at 20:51

0

SELECT SUM( LENGTH(words) - LENGTH(REPLACE(words, ' ', ''))+1)
FROM table

edited Oct 30 '12 at 20:51

Bot

11,868
11
75
131

answered Oct 30 '12 at 20:35

xexodiiax

11

Welcome to Stack Overflow! Rather than only post a block of code, please *explain* why this code solves the problem posed. Without an explanation, this is not an answer. – Martijn Pieters Oct 30 '12 at 20:54
This query appears to make a few assumptions that don't fit the question's data provided. For example that every single space character encountered should indicate a word (note the row where `id` = 4) – Joshua Berry Oct 30 '12 at 21:00

Count words in column with SQL

2 Answers2