pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsubstr_count
5+ messages / 5 participants
[nested] [flat]
* substr_count
@ 2003-07-10 04:38 Tom Rochester <tomr@chu.com.au>
2003-07-10 13:43 ` Re: substr_count Rod Taylor <rbt@rbt.ca>
2003-07-10 15:36 ` Re: substr_count scott.marlowe <scott.marlowe@ihs.com>
2003-07-11 09:53 ` Re: substr_count Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 3 replies; 5+ messages in thread
From: Tom Rochester @ 2003-07-10 04:38 UTC (permalink / raw)
To: pgsql-sql
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."
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substr_count
2003-07-10 04:38 substr_count Tom Rochester <tomr@chu.com.au>
@ 2003-07-10 13:43 ` Rod Taylor <rbt@rbt.ca>
2 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2003-07-10 13:43 UTC (permalink / raw)
To: Tom Rochester <tomr@chu.com.au>; +Cc: pgsql-sql
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, '')))
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substr_count
2003-07-10 04:38 substr_count Tom Rochester <tomr@chu.com.au>
@ 2003-07-10 15:36 ` scott.marlowe <scott.marlowe@ihs.com>
2 siblings, 0 replies; 5+ messages in thread
From: scott.marlowe @ 2003-07-10 15:36 UTC (permalink / raw)
To: Tom Rochester <tomr@chu.com.au>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substr_count
2003-07-10 04:38 substr_count Tom Rochester <tomr@chu.com.au>
@ 2003-07-11 09:53 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rajesh Kumar Mallah @ 2003-07-11 09:53 UTC (permalink / raw)
To: Tom Rochester <tomr@chu.com.au>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: substr_count
@ 2003-07-10 12:55 Michael Pohl <pgsql@newtopia.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Pohl @ 2003-07-10 12:55 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Rochester <tomr@chu.com.au>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-07-11 09:53 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-10 04:38 substr_count Tom Rochester <tomr@chu.com.au>
2003-07-10 13:43 ` Rod Taylor <rbt@rbt.ca>
2003-07-10 15:36 ` scott.marlowe <scott.marlowe@ihs.com>
2003-07-11 09:53 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-07-10 12:55 Re: substr_count Michael Pohl <pgsql@newtopia.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox