Re: how to count string occurrence in column
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
Import Notes
Reply to msg id not found: 006b01c24dad$8e34c860$aa0f5ac2@canaan.co.il
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:
Show quoted text
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.
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:
Show quoted text
yes, this is what i want.
but this dont work :(
pg - 7.1.3Lee 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.
Import Notes
Reply to msg id not found: 002001c24de3$960944e0$aa0f5ac2@canaan.co.il
yes, this is what i want.
but this dont work :(
pg - 7.1.3
Show quoted text
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?