I have the following table
code number
XY1 N2 2
A2 O1 3
KB11 2HG 2
XY2 J2 3
A1 X2 5
I need to select the substring from code until the first integer, i.e. XY1 N2 -> XY, and then aggregate and sum the numbers for these entries, i.e. for XY 2+3, and for A 5+3.
I tried
SELECT LEFT(code,position('(1|2|3|4|5|6|7|8|9|0)' in code)-1) FROM listing;
but it doesn't work. Any suggestions?
This query looks like it will do what you need:
SELECT
substring(code, 1, strpos(code, substring(code FROM '(1|2|3|4|5|6|7|8|9|0)'))-1)AS sub1,
sum(number)
FROM listing
group by substring(code, 1, strpos(code, substring(code FROM '(1|2|3|4|5|6|7|8|9|0)'))-1)
;
Here is the answer you are looking for:
select left(code, (select min(position(g::char in code))-1
from generate_series(0,9) g
where position(g::char in code)>0)) as key, sum(number)
from listing group by key;
Hope it helps!!
By help of regular expression match the below simple query will provide desired result.
select
substring(code,'\D+') as c,
sum(number)
from listing
group by c
