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.
In response to
pgsql-performance by date
|Next:||From: Joshua D. Drake||Date: 2009-02-05 16:13:38|
|Subject: Re: suggestions for postgresql setup on Dell 2950 ,
|Previous:||From: hubert depesz lubaczewski||Date: 2009-02-05 14:13:35|
|Subject: Re: Number of occurrence of characters?|