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

[Ian Norton]

On Thu, Jul 03, 2003 at 11:17:58PM +0100, Paul wrote:
> 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.

Thanks paul,

exactly it :-D works a treat,

bredroll

-- 
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GCS dpu s--: a-- C++++ UL++ P+++ L+++ E--- W-- N+ o K w---
O M-- V-- PS++ PE-- Y+ PGP+ t+++ 5++ X++ R+++ !tv b DI D----
G++ e+ h++ r++ y+++
------END GEEK CODE BLOCK------
----- Message of the Hour ------
Moooo :-)

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