By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with barcodes as (
select *
from
(values
('SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER')
, ( 'SOMETEXTJ9H5G0L2J0OTHERSTUFF')
, ( 'SOMETEXTA0A 0A0OTHERSTUFF' )
)b(barcode)
), counts as (
select *
from barcodes b
left join lateral (
select generate_series as n
from generate_series(1, regexp_count(barcode, '[A-Z](?=\d[A-Z]\s*\d[A-Z]\d)'))
) c on true
),
positions as (
select barcode, regexp_instr(barcode, '[A-Z](?=\d[A-Z]\s*\d[A-Z]\d)', 1, n) as start
from counts
)
select barcode, regexp_substr(barcode, '[A-Z]\d[A-Z]\s*\d[A-Z]\d', start) as postcode
from positions
| barcode | postcode |
|---|---|
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | B1A0A0 |
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | A0A0A0 |
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | B2A 0B0 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | J9H5G0 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | H5G0L2 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | G0L2J0 |
| SOMETEXTA0A 0A0OTHERSTUFF | A0A 0A0 |
SELECT 7
with barcodes as (
select *
from
(values
('SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER')
, ( 'SOMETEXTJ9H5G0L2J0OTHERSTUFF')
, ( 'SOMETEXTA0A 0A0OTHERSTUFF' )
)b(barcode)
)
select barcode, regexp_substr(barcode, '[A-Z]\d[A-Z]\s*\d[A-Z]\d', regexp_instr(barcode, '[A-Z](?=\d[A-Z]\s*\d[A-Z]\d)', 1, n)) as postcode
from barcodes b
left join lateral (
select generate_series as n
from generate_series(1, regexp_count(barcode, '[A-Z](?=\d[A-Z]\s*\d[A-Z]\d)'))
) c on true
| barcode | postcode |
|---|---|
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | B1A0A0 |
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | A0A0A0 |
| SOMETEXTB1A0A0A0OTHERSTUFFB2A 0B0OTHER | B2A 0B0 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | J9H5G0 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | H5G0L2 |
| SOMETEXTJ9H5G0L2J0OTHERSTUFF | G0L2J0 |
| SOMETEXTA0A 0A0OTHERSTUFF | A0A 0A0 |
SELECT 7