UK Postcodes in SQL Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

UK Postcodes in SQL Table

Author

Topic

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-03-20 : 08:28:52

Hi

Been searching for a solution to a problem we have but so far no luck.

We have a Postcode column in our customer.dbo SQL table. This is for the Post/Zip code of their address.

Our couriers are wanting the Postcode to be in a certain format. Example

BS225DU in the field needs to be BS22 5DU

The problem is that UK postcodes all have different lengths..example

W1 5DU
BS22 4LX
C22 3GT

One common factor is that they all end in 3 digits that are Numeric, alpha, alpha

Is there any SQL script that someone knows of that can convert the postcodes to upper case and add a space fourth character to the left from the end so that the postcodes are split correctly?

Any suggestions would be welcome!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-20 : 09:16:10

-- testtable and testdata
declare @sample table(postcode varchar(255))
insert @sample
select 'W15DU' union all
select 'Bs224LX' union all
select 'C223gT'

-- solution
select
postcode as orig,
upper(reverse(stuff(reverse(postcode),4,0,' '))) as formatted
from @sample

--edit: added UPPER()

Too old to Rock'n'Roll too young to die.

deanglen
Yak Posting Veteran

65 Posts

Posted - 2013-03-20 : 10:45:21

Thanks I can see the logic worked fine. So to replace the column values Zip in Address.dbo what would be the script to run?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-20 : 10:54:51

I would not replace the values in the table. I would do the formatting on the fly when selecting the data.

But if you want to change the data in your table then (have a backup) do this:

update YourTable
set PostcodeColumn = upper(reverse(stuff(reverse(PostcodeColumn),4,0,' ')))

Maybe you can add a where clause to change only UK postcodes?

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources