Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- sample table
CREATE TABLE account_code(
   account_code_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
 , accountname text NOT NULL
 , charge_amount numeric
);

INSERT INTO account_code(accountname, charge_amount) VALUES
   ('ADDICTION ADVICE Blub',  100)
 , ('ADDICTION ADVICE'     ,   10)
 , ('ADDICTION ADVICES'    ,    1)
 , ('ADDICTION AD'         ,    0)
 , ('AIR NEW Z'            , 1000)
 , ('AIR NEW Ze'           ,  100)
 , ('AIR NEW ZeeLANT'      ,   10)
 , ('AIR NEW ZEALAND'      ,    1)
 , ('AIR NEW'              ,    0)
 , ('NO PATTERN HERE'      ,   10)
 , ('NO PATTERN HERE'      ,    1)
;
11 rows affected

-- translation table
CREATE TABLE ac_translate (
   ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , like_pattern text NOT NULL
 , target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES 
   ('AIR NEW Z%'       , 'AIR NEW ZEALAND') -- rows in order of precedence!
 , ('AIR BP%'          , 'AIR BP')
 , ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
 , ('AIA%'             , 'AIA')
;
4 rows affected

SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;


accountname
sum_amount


ADDICTION AD
0

ADDICTION ADVICE
111

AIR NEW
0

AIR NEW ZEALAND
1111

NO PATTERN HERE
11


SELECT COALESCE(
        (SELECT a1.target
         FROM   ac_translate a1
         WHERE  ac.accountname LIKE a1.like_pattern
         ORDER  BY a1.ord_nr
         LIMIT  1), ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;


accountname
sum_amount


ADDICTION AD
0

ADDICTION ADVICE
111

AIR NEW
0

AIR NEW ZEALAND
1111

NO PATTERN HERE
11


CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));
SET enable_seqscan = off;

EXPLAIN
SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  left(ac.accountname, 3) = left(a1.like_pattern, 3)
   AND    ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;


QUERY PLAN


GroupAggregate  (cost=10000007019.23..10000007028.11 rows=200 width=64)

  Group Key: (COALESCE(a1.target, ac.accountname))

  ->  Sort  (cost=10000007019.23..10000007021.36 rows=850 width=64)

        Sort Key: (COALESCE(a1.target, ac.accountname))

        ->  Nested Loop Left Join  (cost=10000000008.16..10000006977.88 rows=850 width=64)

              ->  Seq Scan on account_code ac  (cost=10000000000.00..10000000018.50 rows=850 width=64)

              ->  Limit  (cost=8.16..8.17 rows=1 width=36)

                    ->  Sort  (cost=8.16..8.17 rows=1 width=36)

                          Sort Key: a1.ord_nr

                          ->  Index Scan using ac_translate_left_idx on ac_translate a1  (cost=0.13..8.15 rows=1 width=36)

                                Index Cond: ("left"(ac.accountname, 3) = "left"(like_pattern, 3))

                                Filter: (ac.accountname ~~ like_pattern)
accountname	sum_amount
ADDICTION AD	0
ADDICTION ADVICE	111
AIR NEW	0
AIR NEW ZEALAND	1111
NO PATTERN HERE	11