Re: how to count string occurrence in column

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