How to count words in MySQL / regular expression replacer?

mysql regex replace word-count
PierrOz picture PierrOz · Nov 18, 2009 · Viewed 28.8k times · Source

How can I, in a MySQL query, have the same behaviour as the Regex.Replace function (for instance in .NET/C#)?

I need that because, as many people, I would like to count the number of words in a field. However, I'm not satisfied with the following answer (given several times on that site):

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

Because it doesn't give good results when there are more that one space between two words.

By the way, I think the Regex.Replace function may be interesting so all the good ideas are welcome !

Answer

laalto picture laalto · Nov 18, 2009

There's REGEXP_REPLACE available as MySQL user-defined functions.

Word counting: If you can control the data going into the database, you can remove double whitespace before insert. Also if you have to access the word count often, you can compute it once in your code and store the count in the database.

Related questions

Is there a MySQL equivalent of PHP's preg_replace?

I have a to match a field in MySQL, for which I thought I could use a regular expression, but it appears that MySQL doesn't have the functionality I need to do the job. Here's the scenario: I have a …

Read More
How to do a regular expression replace in MySQL?

I have a table with ~500k rows; varchar(255) UTF8 column filename contains a file name; I'm trying to strip out various strange characters out of the filename - thought I'd use a character class: [^a-zA-Z0-9()_ .\-] Now, is there …

Read More
How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More