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
