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