Thread: A function to count all ocurrences of a character within a string.
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 -- -------------------------------------------------------------- "TECHBAZA.PL" Sp. z o.o. Technologie WEB, eDB& eCommerce tel. (+4832) 7186081 fax. (+4832) 7003289 email: biuro@techbaza.pl
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
Re: A function to count all ocurrences of a character within a string.
From
"Ross J. Reedstrom"
Date:
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