determine how many matches of a string in a field - Mailing list 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