[Gllug] OT: SQL counting regex matches within a field

[Paul]

On Thu, 2003-07-03 at 19:32, Ian Norton wrote:
> does anyone know how to cound the number of times a match occurs within a
> field in mysql?

So for example if you have rows "abracadabra", "aardvark" and "cat" and
your match expression is the letter "a" you'd want a function to return
5, 3, 1 respectively?

How about:

SELECT
(LENGTH(col) - LENGTH(REPLACE(col, 'expr', ''))) / LENGTH('expr')
FROM table

where you want to count the number of occurrences of the expression
'expr' in the column 'col'.

Paul.


-- 
Gllug mailing list  -  Gllug at linux.co.uk
http://list.ftech.net/mailman/listinfo/gllug