Re: Regular expression and Group By

On Fri, 2006-12-15 at 17:21 -0200, Clodoaldo wrote:
> -- drop table test_table;
> create table test_table (tname varchar, value integer);
> insert into test_table values ('[ab]x', 1);
> insert into test_table values ('[ab]y', 2);
> insert into test_table values ('[Ab]z', 3);
> insert into test_table values ('w[aB]', 8);
> insert into test_table values ('[abx', 4);
> insert into test_table values ('ab]x', 5);
> insert into test_table values ('xyz', 6);
> insert into test_table values ('Xyz', 7);
>
...
> total | tname | value_total
> -------+-------+-------------
> 4 | [AB] | 14
> 1 | [abx | 4
> 1 | ab]x | 5
> 1 | xyz | 6
> 1 | Xyz | 7
> (5 rows)
>

It looks like what you need is a function that returns the upper() of
the substring enclosed by "[]" if that substring exists, or else the
original string.

In other words, make a user-defined function that turns the string into
"tname" and GROUP BY the result of that function.

Hope this helps,
Jeff Davis