Thread: substr_count
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."
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, '')))
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.
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
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.