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