From: Ross J. Reedstrom <reedstrm@rice.edu>
To: pgsql-sql@postgresql.org
Subject: Re: A function to count all ocurrences of a character within a string.
Date: Mon, 7 Mar 2011 16:32:41 -0600
Message-ID: <20110307223241.GB22002@rice.edu> (raw)
In-Reply-To: <AANLkTimkG6PrtaS7MQ8qxEuMRLFre6ZxOXtgfApb+OgE@mail.gmail.com>
References: <4D754C0E.4030804@techbaza.pl>
<AANLkTimkG6PrtaS7MQ8qxEuMRLFre6ZxOXtgfApb+OgE@mail.gmail.com>
On Mon, Mar 07, 2011 at 02:08:10PM -0800, bricklen wrote:
> On Mon, Mar 7, 2011 at 1:20 PM, Piotr Czekalski <pczekalski@techbaza.pl> wrote:
> > Hello pgsql community,
> >
> > Is there any string function (other than regex / scan & compare loop) to
> > obtain a list (or even a count) of characters within a string?
> > strpos and position seems to return only first occurence of a char/string
> > within substring.
> > The goal is to check if a string contains equal number of opening and
> > closing parenthesis, and if there are any of them.
> >
> > Regards,
> >
> > Piotr Czekalski
>
> Couple examples here that might help to get you started
> http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Get_count_of_substrings_in_string
Oh, I like that trick! A little substitution and rearranging leads to:
CREATE OR REPLACE FUNCTION balanced_parens(text)
RETURNS integer AS $$
SELECT(length(replace($1, '(', '')) - length(replace($1,')',''))) ;
$$ LANGUAGE SQL IMMUTABLE;
Generalizing (curly braces? brackets?)
CREATE OR REPLACE FUNCTION balanced_pairs(text,text,text)
RETURNS integer AS $$
SELECT(length(replace($1, $2, '')) - length(replace($1,$3,''))) ;
$$ LANGUAGE SQL IMMUTABLE;
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: reedstrm@rice.edu
Subject: Re: A function to count all ocurrences of a character within a string.
In-Reply-To: <20110307223241.GB22002@rice.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox