Message-ID: <3F0CEDC8.9000304@chu.com.au>
Date: Thu, 10 Jul 2003 14:38:32 +1000
From: Tom Rochester <tomr@chu.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.2) Gecko/20021216
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: substr_count
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hey all,

I would like to achive something along the lines of:

SELECT field FROM table WHERE field ILIKE '$searchterm' ORDER BY 
substr_count(field, '$searchterm');

Of course the substr_count function does not exist. Is there anyway to 
do this?

I had a thought char_count(replace(field, !$searchterm, '')) might do 
the job but replace doesn't allow for 'replace everything NOT string';

Any Ideas?

Thanks in advance,

-- 
Tom Rochester
Systems Developer/Administrator

CHU Underwriting Agencies Pty Ltd
Level 5, 1 Northcliff Street, 
Milsons Point  NSW  2060

<P>     02 8923 5346
<F>     02 8923 5363
<M>	+61416152792
<E>     tomr@chu.com.au
<W>     http://www.chu.com.au/

"The information contained in this email and any attached files is strictly private and confidential. The intended addressee should read this email only. If the recipient of this message is not the intended addressee, please call CHU Underwriting Agencies Pty Ltd on +61 1300 361 263 and promptly delete this email and any attachments.

The intended recipient of this email may only use, reproduce, disclose or distribute the information contained in this email and any attached files with CHU's permission. If you are not the intended addressee, you are strictly prohibited from using, reproducing, disclosing or distributing the information contained in this email and any attached files.

CHU advises that this email and any attached files should be scanned to detect viruses. CHU accepts no liability for loss or damage (whether caused by negligence or not) resulting from the use of any attached files."