Ivan Voras wrote:
> I have a need to fairly often select data where the number of
> occurrences of a character in the field is "x". Semantically, it's
> literally "SELECT something FROM table WHERE numch('/', field)=$x".
>
> The problem is how to do it efficiently. I see there isn't a built-in
> function that counts character occurrences so I'd have to write it
> myself. An additional constraint is that it must be implemented with
> built-in capabilities, i.e. SQL and plpsql languages. I can do it the
> brute force way, looping over the string and processing one by one
> character with substring(), but is there a faster way?
Hmm, you could do this:
CREATE OR REPLACE FUNCTION numch(text, text) RETURNS integer AS $$
SELECT length($2) - length(replace($2, $1, '')) $$ LANGUAGE SQL;
ie. remove the characters we're counting, and see how much shorter the
string became. I don't know if this is any faster than looping in a
plpgsql function, but it might be.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com