Thread: how to count string occurrence in column

Hi All


How can i count how many time a string 'hello' appear in a column.

for example.

select *, count_num_of_string(column, 'hello') from table;

Cheer

On Tue, 27 Aug 2002, Ben-Nes Michael wrote:
> Hi All
>
>
> How can i count how many time a string 'hello' appear in a column.
>
> for example.
>
> select *, count_num_of_string(column, 'hello') from table;

SELECT colname, count(1) FROM mytable WHERE colname = 'hello';

or case insensitively

SELECT colname, count(1) FROM mytable WHERE lower(colname) = 'hello';

or get a list of frequencies, most frequent listed first:

SELECT colname, count(1) FROM mytable GROUP BY colname ORDER BY 2 DESC;


I don't know any good books on SQL but I suggest you see what you can
find. Of course there may well be some tutorials available on the web.


--
Nigel J. Andrews
Director

---
Logictree Systems Limited
Computer Consultants

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...

Lee.

Nigel J. Andrews writes:
 >
 > On Tue, 27 Aug 2002, Ben-Nes Michael wrote:
 > > Hi All
 > >
 > >
 > > How can i count how many time a string 'hello' appear in a column.
 > >
 > > for example.
 > >
 > > select *, count_num_of_string(column, 'hello') from table;
 >
 > SELECT colname, count(1) FROM mytable WHERE colname = 'hello';
 >
 > or case insensitively
 >
 > SELECT colname, count(1) FROM mytable WHERE lower(colname) = 'hello';
 >
 > or get a list of frequencies, most frequent listed first:
 >
 > SELECT colname, count(1) FROM mytable GROUP BY colname ORDER BY 2 DESC;
 >
 >
 > I don't know any good books on SQL but I suggest you see what you can
 > find. Of course there may well be some tutorials available on the web.
 >
 >
 > --
 > Nigel J. Andrews
 > Director
 >
 > ---
 > Logictree Systems Limited
 > Computer Consultants
 >
 >
 > ---------------------------(end of broadcast)---------------------------
 > TIP 4: Don't 'kill -9' the postmaster

On 27 Aug 2002 at 10:35, Lee Kindness wrote:
> Actually i'm sure the original poster is after something like:
>
>  SELECT count('qwerty qwert qwertyffff fff qq', 'qwerty');
>   count
>  --------
>        2
>  (1 row)

I think this should do it:

select count (*) from table where columnname ~* 'substring';

On 27 Aug 2002 at 10:35, Lee Kindness wrote:
> 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.

yes, this is what i want.

but this dont work :(
pg - 7.1.3

> > 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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.