How to extract postcode/digits from one column and put it in to another column in SQL Oracle 9i

Question

Table ADRESSES contains columns [ ID, STREET, TOWN, POSTCODE,COUNTRY ].

Hot to make statement in Oracle 9i database:

if column COUNTRY contains string 'UK', and POSTCODE is empty but column TOWN starts with post code (digits in format xxxxx or xx-xxx), then move postal code to column POSTCODE and strip post code from column TOWN.

Answer 1

Below are two sql statements, one to select, and see the impact of the update statement, and the update statement. Back up your tables before you run any updates and always test that the update is doing what you expect by running it as a select statement first.

Note: I made the assumption that you want to keep the postcode in the format it was originally entered. eg if it was entered as xxxxx it will be saved as xxxxx and if it was entered as xx-xxx it will be saved as xx-xxx.

Check to see what the update statement will do:

select ID, STREET, TOWN, POSTCODE, COUNTRY, 
case when substr(TOWN, 3, 1) = '-' like '%-%' then trim(substr(TOWN,7, length(TOWN)-6)) else trim(substr(TOWN,6, length(TOWN)-5)) end as NEW_TOWN,
case when substr(TOWN, 3, 1) = '-' like '%-%' then substr(TOWN, 1, 6) else substr(TOWN, 1, 5) end as NEW_POSTCODE--Assumes you want to keep the dash if it exists
from ADDRESSES
where COUNTRY like'%UK%' --contains string UK
and trim(POSTCODE) is null -- postcode is empty
and (
    length(trim(translate(substr(TOWN, 1, 5), '0123456789', ' '))) is null -- town starts with xxxxx digits
    or
    (length(trim(translate(substr(TOWN, 1, 2)||substr(TOWN,4,3, '0123456789', ' '))))  and  substr(TOWN, 3, 1) = '-') -- town starts with xx-xxx digits
    )
;

If you are satisfied, run the update statement.

update ADDRESSES
set
TOWN = case when substr(TOWN, 3, 1) = '-' like '%-%' then trim(substr(TOWN,7, length(TOWN)-6)) else trim(substr(TOWN,6, length(TOWN)-5)) end,
POSTCODE = case when substr(TOWN, 3, 1) = '-' like '%-%' then substr(TOWN, 1, 6) else substr(TOWN, 1, 5) end --Assumes you want to keep the dash if it exists
from ADDRESSES
where COUNTRY like'%UK%' --contains string UK
and trim(POSTCODE) is null -- postcode is empty
and (
    length(trim(translate(substr(TOWN, 1, 5), '0123456789', ' '))) is null -- town starts with xxxxx digits
    or
    (length(trim(translate(substr(TOWN, 1, 2)||substr(TOWN,4,3, '0123456789', ' '))))  and  substr(TOWN, 3, 1) = '-') -- town starts with xx-xxx digits
    )
;

I hope this will at least serve as a starting point.