Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id C48691337B4E for ; Mon, 7 Mar 2011 18:32:52 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 67286-01 for ; Mon, 7 Mar 2011 22:32:43 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from cooker.cnx.rice.edu (cooker.cnx.rice.edu [168.7.5.70]) by mail.postgresql.org (Postfix) with ESMTP id 52B98133616B for ; Mon, 7 Mar 2011 18:32:42 -0400 (AST) Received: from reedstrm by cooker.cnx.rice.edu with local (Exim 4.69) (envelope-from ) id 1Pwiyn-00081Y-KH for pgsql-sql@postgresql.org; Mon, 07 Mar 2011 16:32:41 -0600 Date: Mon, 7 Mar 2011 16:32:41 -0600 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: A function to count all ocurrences of a character within a string. Message-ID: <20110307223241.GB22002@rice.edu> References: <4D754C0E.4030804@techbaza.pl> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201103/13 X-Sequence-Number: 35215 On Mon, Mar 07, 2011 at 02:08:10PM -0800, bricklen wrote: > On Mon, Mar 7, 2011 at 1:20 PM, Piotr Czekalski 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