[GENERAL] Count occurrences of pattern in string

[dario....@libero.it]

Hello,

I'm looking for a function to count the occurrences of a pattern in a 
string. E.g. something like:

fun_count_pattern('fooXblaX', 'X')

which would 
return 2 (I.e. pattern 'X' found 2 times in string 'fooXblaX').

I could write 
my own function for this (probably using plpython?) but I was wandering whether 
there is some function or combination of functions that I could use 'off-the-
shelf'.

Thanks very much

All the best

Dario

PS: I'm using PostgreSQL 8.3 on 
Windows XP.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Count occurrences of pattern in string

[Andreas Kretschmer]

dario@libero.it  wrote:

> Hello,
> 
> I'm looking for a function to count the occurrences of a pattern in a 
> string. E.g. something like:
> 
> fun_count_pattern('fooXblaX', 'X')
> 
> which would 
> return 2 (I.e. pattern 'X' found 2 times in string 'fooXblaX').

How about:

test=*# select length('fooXblaX') - 
length(regexp_replace('fooXblaX','X','','g')) / length('X');
 ?column?
--
2
(1 Zeile)



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general