Skip site navigation (1) Skip section navigation (2)

Re: Number of occurrence of characters?

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Ivan Voras <ivoras(at)freebsd(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Number of occurrence of characters?
Date: 2009-02-05 14:35:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Joshua D. DrakeDate: 2009-02-05 16:13:38
Subject: Re: suggestions for postgresql setup on Dell 2950 , PERC6i controller
Previous:From: hubert depesz lubaczewskiDate: 2009-02-05 14:13:35
Subject: Re: Number of occurrence of characters?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group