How to extract postcode/digits from one column and put it in to another column in SQL Oracle 9i
NickName:dawciobiel Ask DateTime:2015-02-20T19:31:56

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

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.

Copyright Notice：Content Author:「dawciobiel」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/28627960/how-to-extract-postcode-digits-from-one-column-and-put-it-in-to-another-column-i

Answers
Ghat Workman 2015-02-20T13:50:42

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. \n\nNote: 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. \n\nCheck to see what the update statement will do: \n\nselect ID, STREET, TOWN, POSTCODE, COUNTRY, \ncase 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,\ncase 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\nfrom ADDRESSES\nwhere COUNTRY like'%UK%' --contains string UK\nand trim(POSTCODE) is null -- postcode is empty\nand (\n length(trim(translate(substr(TOWN, 1, 5), '0123456789', ' '))) is null -- town starts with xxxxx digits\n or\n (length(trim(translate(substr(TOWN, 1, 2)||substr(TOWN,4,3, '0123456789', ' ')))) and substr(TOWN, 3, 1) = '-') -- town starts with xx-xxx digits\n )\n;\n\n\nIf you are satisfied, run the update statement. \n\nupdate ADDRESSES\nset\nTOWN = case when substr(TOWN, 3, 1) = '-' like '%-%' then trim(substr(TOWN,7, length(TOWN)-6)) else trim(substr(TOWN,6, length(TOWN)-5)) end,\nPOSTCODE = 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\nfrom ADDRESSES\nwhere COUNTRY like'%UK%' --contains string UK\nand trim(POSTCODE) is null -- postcode is empty\nand (\n length(trim(translate(substr(TOWN, 1, 5), '0123456789', ' '))) is null -- town starts with xxxxx digits\n or\n (length(trim(translate(substr(TOWN, 1, 2)||substr(TOWN,4,3, '0123456789', ' ')))) and substr(TOWN, 3, 1) = '-') -- town starts with xx-xxx digits\n )\n;\n\n\nI hope this will at least serve as a starting point. ",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “How to extract postcode/digits from one column and put it in to another column in SQL Oracle 9i” related questions
How to extract postcode/digits from one column and put it in to another column in SQL Oracle 9i

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 ...

Show Detail
Excel How to extract two digits from Column A

I have a question about Excel How Can I extract two digits from Column B if Column B is empty extract two digits from column A. I know how to extract two digit from Column B but cannot understand h...

Show Detail
Why column with null value come before column with values when you order by a timestamp column in oracle 9i

I've got a strange behaviour with an Oracle 9i database. If i make a query like this: select * from table order by dp_dt_timestamp DESC; where dp_dt_timestamp is a timestamp column, rows which ...

Show Detail
Using Oracle 9i, how can I extract the first number from a string?

I need to extract the first integer number found in a column of a database. Unfortunately, I am stuck using data from an Oracle 9i database, which doesn't support regex. Some data examples: 'Count ...

Show Detail
Is there a way in pyspark to extract digits /alphabets from alphanumeric column

I want to extract the numerical digits from an alphanumeric string column into another column which will contain only digits and not alphabets using pyspark.

Show Detail
Extract a set of digits from a string column in MySQL

I am trying to extract a fixed-length digits from a string column in MySQL, how can I go about doing so? The usual like and regexp doesn't work, so am pretty certain a function is needed, but unsure

Show Detail
Extract substring from column based on values from another table in SQL

In SQL (let's assume ANSI standard SQL), is it possible to extract a substring from one column, based on another table, and put this substring into another column? Example, from the following tabl...

Show Detail
Add a column SQL query in Oracle database

I am using Oracle Database (version is 9i) and I want to add a column to a current table in oracle database. I want to add an integer column to keep track of invalid tries per user, so default value

Show Detail
Oracle SQL Developer and Oracle 9i version

I'm using Oracle SQL Developer version: When i try execute a SELECT in a Oracle 9i database i got the following error: I have PL/SQL DEVELOPER installed in my machine and i can SELECT this same ...

Show Detail
how to extract date from string in sql oracle 9i

I have column Explanation (VARCHAR2) in table: No| Explanation 1 | Shipment of cabbage by agreement 29.04.2019 TAX Free 2 | Payment for screws (01.04.19) Tax: 13.55 % 3 | For reserch by deal...

Show Detail