SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 UK Postcodes in SQL Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

deanglen
Yak Posting Veteran

64 Posts

Posted - 03/20/2013 :  08:28:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 03/20/2013 :  09:16:10  Show Profile  Visit webfred's Homepage  Reply with Quote
-- 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.

Edited by - webfred on 03/20/2013 10:40:47
Go to Top of Page

deanglen
Yak Posting Veteran

64 Posts

Posted - 03/20/2013 :  10:45:21  Show Profile  Reply with Quote
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?



Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 03/20/2013 :  10:54:51  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000