Thread: substr_count

substr_count

From
Tom Rochester
Date:
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
addresseeshould read this email only. If the recipient of this message is not the intended addressee, please call CHU
UnderwritingAgencies 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
emailand any attached files with CHU's permission. If you are not the intended addressee, you are strictly prohibited
fromusing, 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
lossor damage (whether caused by negligence or not) resulting from the use of any attached files."
 




Re: substr_count

From
Rod Taylor
Date:
On Thu, 2003-07-10 at 04:38, Tom Rochester wrote:
> 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';

How about:

(char_count(field) - char_count(replace(field, $searchterm, '')))

Re: substr_count

From
"scott.marlowe"
Date:
On Thu, 10 Jul 2003, Tom Rochester wrote:

> 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';

You'll likely have to code a bit in pl/pgsql.  The regex capable substring 
of 7.3 might come in handy.



Re: substr_count

From
Michael Pohl
Date:
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



Re: substr_count

From
Rajesh Kumar Mallah
Date:
On Thursday 10 Jul 2003 10:08 am, Tom Rochester wrote:
> 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');
>


Hi In case you are  attempting to search text in a feild 
and sort it by relevence then contrib/tsearch V2 is
for you.

http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/


regds
mallah.



> 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,

-- 
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)

Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.