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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
