Re: counting words in a text or char varying field

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> i'm looking for a way to count the words in text field or char varying
> separated by white spaces within postgresql itself.

That depends on what you mean by "postgresql itself". You cannot do this
with ordinary SQL: you are going to need a procedural language. Perl
would make an excellent choice, but that would be too easy :), so I'll
try and come up with a quick plpgsql example. This is only a test script,
and 'whitespace' in this case means, literally, one or more space
characters. It's a start. :)

CREATE FUNCTION countwords(TEXT) RETURNS INTEGER AS '
DECLARE
mystring ALIAS FOR $1;
words INTEGER := 0;
inspace BOOL := true; -- ## Catch the first word
BEGIN
FOR pos IN 1 .. CHAR_LENGTH(mystring) LOOP

-- There are 2 single quotes, a space, and 2 single quotes below:
IF SUBSTRING(mystring,pos,1) = '' '' THEN
inspace := true;
ELSE
IF inspace THEN
inspace := false;
words := words+1;
END IF;
END IF;
END LOOP;
IF inspace is false THEN -- ## Catch the last word
words := words+1;
END IF;

RETURN words;
END;
' LANGUAGE 'plpgsql';

Let's give it a whirl:

select to_char(cdate, 'YYYYMMDD'), countwords(body)
from turnstep_mail where whofrom like '%tom(at)minnesota(dot)com%'
order by 1 desc limit 5;

to_char | countwords
- ----------+------------
20020304 | 493
20020304 | 35
20020303 | 194
20020303 | 74
20020302 | 44

Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200203041730

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE8g/m/vJuQZxSWSsgRAqpfAKDSh7WYjF6l3cWfIw+8O/UPeKgjmwCfRlsc
OI69IIGHlMKMIbR0+Mc/q/g=
=0ral
-----END PGP SIGNATURE-----