X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.193])
	by svr1.postgresql.org (Postfix) with ESMTP id 8DE31CC3C0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 08:32:34 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new,
	port 10024) with ESMTP id 73019-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 11 Jul 2003 05:32:22 -0300 (ADT)
Received: from server1.techplant.com (unknown [209.61.173.33])
	by svr1.postgresql.org (Postfix) with ESMTP id D927ECD0CD
	for <pgsql-sql@postgresql.org>; Thu, 10 Jul 2003 09:54:09 -0300 (ADT)
Received: from localhost (michael@localhost)
	by server1.techplant.com (8.11.6/8.11.6) with ESMTP id h6ACtxB13911;
	Thu, 10 Jul 2003 08:56:04 -0400
Date: Thu, 10 Jul 2003 08:55:59 -0400 (EDT)
From: Michael Pohl <pgsql@newtopia.com>
X-X-Sender: michael@server1.techplant.com
To: pgsql-sql@postgresql.org
Cc: Tom Rochester <tomr@chu.com.au>
Subject: Re: substr_count
In-Reply-To: <Pine.LNX.4.44.0307092104480.6784-100000@server1.techplant.com>
Message-ID: <Pine.LNX.4.44.0307100843370.13741-100000@server1.techplant.com>
X-Mailkey: j9dfjk23j4dkq
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200307/139
X-Sequence-Number: 14253

On Thu, 10 Jul 2003, Tom Rochester wrote:

> I would like to achive something along the lines of:
> 
> SELECT field FROM table WHERE field ILIKE '$searchterm' ORDER BY
> substr_count(field, '$searchterm');

If you have plperl installed:

create or replace function substr_count(
   varchar(255),
   varchar(255)
)
returns int as '
   my ($field, $searchterm) = @_;
   my $count = $field =~ s/$searchterm//g;
   return $count;
' language 'plperl';

michael