From: | "Dave [Hawk-Systems]" <dave(at)hawk-systems(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | determine how many matches of a string in a field |
Date: | 2003-07-29 13:41:42 |
Message-ID: | DBEIKNMKGOBGNDHAAKGNEEMBCNAC.dave@hawk-systems.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
resolved the previous question regarding scoring to some extent...
currently have this select;
SELECT *, (
(CASE WHEN (keywords ~* '.*MySearchString.*') THEN 5 ELSE 0 END) +
(CASE WHEN (title ~* '.*MySearchString.*') THEN 3 ELSE 0 END) +
(CASE WHEN (description ~* '.*MySearchString.*') THEN 1 ELSE 0 END)
) AS score FROM catalog_table WHERE
keywords ~* '.*MySearchString.*' or
title ~* '.*MySearchString.*' or
description ~* '.*MySearchString.*'
AND status='D' ORDER BY score DESC;
this does a great job of finding results that match 1 or more occurances of
MySearchString in the fields selected... I can't figure out how to determine if
multiple results exist in each field, and if so, how to multiply the results.
essentially looking for a legitimate form of this pseudo code;
(CASE WHEN (keywords ~* '.*MySearchString.*') THEN (substring_count(keywords
from '.*MySearchString.*')*5) ELSE 0 END)
thoughts?
Dave
From | Date | Subject | |
---|---|---|---|
Next Message | Franco Bruno Borghesi | 2003-07-29 14:10:39 | Re: Batch processing |
Previous Message | Aspire | 2003-07-29 13:36:45 | Jabber Interface Sucess |