MySQL Bugs: #8098: REGEX match count

Bug #8098	REGEX match count
Submitted:	23 Jan 2005 20:26	Modified:	4 Feb 2009 13:54
Reporter:	Robert Seaborn	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.01	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Currently REGEXP returns 0 for FALSE and 1 for TRUE.  It would be nice if REGEXP returned how many matches it found instead.  That way, a ranked search can be done on the number of matches a keyword or keywords that are found in a VARCHAR or TEXT field.

How to repeat:
This select statement returns only 0 or 1 in the FoundText column.
SELECT field_id, post_text REGEXP 'comment|anything|other' AS FoundText FROM table2search;
Would like for FoundText column to display 0 or the number of matches it found.  That way, if post_text has both 'comment' and 'anything' it will return 2, and if post_text has all of 'comment', 'anything', and 'other' it will return 3.  Perform a sort in the ORDER BY clause in order to sort this simple ranking: 'ORDER BY FoundText DESC'

Suggested fix:
Allow REGEXP to return the number of pattern matches.

Many thanks for writing a feature request.

That you can do such stuff with regular expressions is not familiar in any language.

Just look here to see how it works in PHP:
http://lt.php.net/manual/en/function.preg-match.php

We won't fix this in nearer future, so I will set this to Won't fix.

Also it is possible to use RE with UDFs.

I also support the implementation of this feature request.  I encourage any passers by to add their support too.  This functionality would enhance most databases without the need for individual regexp matches (and thus higher DB load)