By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 'TEST DATA - SCOTLAND 1A' AS input FROM dual UNION ALL
SELECT 'TEST DATA - ENGLAND 6A' FROM dual UNION ALL
SELECT 'TEST DATA - WALES 3A' FROM dual UNION ALL
SELECT 'TEST DATA - IRELAND 2A' FROM dual
)
SELECT
input,
REGEXP_REPLACE(input, '.*- (\D+).*', '\1') AS country
FROM yourTable;
| INPUT | COUNTRY |
|---|---|
| TEST DATA - SCOTLAND 1A | SCOTLAND |
| TEST DATA - ENGLAND 6A | ENGLAND |
| TEST DATA - WALES 3A | WALES |
| TEST DATA - IRELAND 2A | IRELAND |