Regular expression and Group By

Regular expression and Group By

There is a varchar column which I need to group by an "uppered"
substring inside '[]' like in 'xxx[substring]yyy'. All the other lines
should not be changed.

I can do it using a union. I would like to reduce it to a single
query, but after much thought I can't. So I'm asking the regex experts
a hand.

This is how I do it:

-- 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);

select
count(*) as total,
tname,
sum(value) as value_total
from (

select
substring(upper(tname) from E'\\[.*\\]') as tname,
value
from test_table
where tname ~ E'\\[.*\\]'

union all

select tname, value
from test_table
where tname !~ E'\\[.*\\]'

) as a
group by tname
order by tname;

The result which is correct:

total | tname | value_total
-------+-------+-------------
4 | [AB] | 14
1 | [abx | 4
1 | ab]x | 5
1 | xyz | 6
1 | Xyz | 7
(5 rows)

Regards,
--
Clodoaldo Pinto Neto