UPDATE: Have now added a separate answer for MySQL 8.0+, which should be used in preference. (Retained this answer in case of being constrainted to using an earlier version.)
Almost a duplicate of this question but this answer will address the use case of counting words based on the advanced version of the custom regular expression replacer from this blog post.
Demo
Rextester online demo
For the sample text, this gives a count of 61 - the same as all online word counters I've tried (e.g. https://wordcounter.net/).
SQL (excluding function code for brevity):
SELECT txt,
-- Count the number of gaps between words
CHAR_LENGTH(txt) -
CHAR_LENGTH(reg_replace(txt,
'[[:space:]]+', -- Look for a chunk of whitespace
'^.', -- Replace the first character from the chunk
'', -- Replace with nothing (i.e. remove the character)
TRUE, -- Greedy matching
1, -- Minimum match length
0, -- No maximum match length
1, -- Minimum sub-match length
0 -- No maximum sub-match length
))
+ 1 -- The word count is 1 more than the number of gaps between words
- IF (txt REGEXP '^[[:space:]]', 1, 0) -- Exclude whitespace at the start from count
- IF (txt REGEXP '[[:space:]]$', 1, 0) -- Exclude whitespace at the end from count
AS `word count`
FROM tbl;