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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
