Re: how to count string occurrence in column

Well don't say i'm anything but kind! Assuming you can use PL/PGSQL
then the following SQL should be exactly what you're looking for. I've
run it on 7.2 with expected results (I guess you want 'CREATE
FUNCTION' rather than 'CREATE OR REPLACE FUNCTION' for 7.1 though)...

Anyway:

\echo creating function: count_substring
CREATE OR REPLACE FUNCTION count_substring(VARCHAR, VARCHAR) RETURNS INTEGER AS '
DECLARE
sub ALIAS FOR $2;
str VARCHAR;
pos INTEGER;
total INTEGER;
BEGIN
str := $1;
total := 0;
LOOP
pos := strpos(str, sub);
IF pos = 0 THEN
RETURN total;
ELSE
total := total + 1;
str = substr(str, pos + 1);
END IF;
END LOOP;
RETURN total;
END;
' LANGUAGE 'plpgsql';

\echo creating table: tab
DROP TABLE tab;
CREATE TABLE tab(data VARCHAR);

\echo inserting: tab
INSERT INTO tab(data) VALUES('str sffs');
INSERT INTO tab(data) VALUES('strstr');
INSERT INTO tab(data) VALUES('strstrstr');
INSERT INTO tab(data) VALUES('sxx');

\echo querying:
SELECT data, count_substring(data, 'str') FROM tab;
SELECT SUM(count_substring(data, 'str')) FROM tab;

Ben-Nes Michael writes:
> yes, this is what i want.
> but this dont work :(
> pg - 7.1.3
> > Lee Kindness writes:
> > > Actually i'm sure the original poster is after something like:
> > > SELECT count('qwerty qwert qwertyffff fff qq', 'qwerty');
> > > count
> > > --------
> > > 2
> > > (1 row)
> > > But off the top of my head i cannot think of a suitable standard
> > > function...
> > Woops, posted too quick. Maybe my posting doesn't answer the original
> > question:
> > column1
> > row 1 'test test'
> > row 2 'test'
> > My statement would count 2. Maybe the OP would want 3. In that case, I
> > think you'll have to write some code that counts substring occurences.