Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Clean up postcodes 1

Status
Not open for further replies.

katehol

Technical User
May 2, 2007
57
GB
Hi

I am stuck on a query where I need to clean up the postcode data we have been given by a client. Some postcodes are in the proper format (eg AA1 1AA or A1 1AA or AA11 1AA). But some are in the incorrect format (eg AA11AA or A11AA or AA111AA).

Does anyone know of a way to update all of the postcodes that don't have blank spaces, so that they have the blank space inserted before the 3rd from last character? I am stumped.

Thanks :)
 
Try this select (to make sure you get the correct data).

Code:
Select PostCode, Stuff(PostCode, Len(PostCode)-2, 0, ' ')
From   YourTable
Where  PostCode Not Like '% ___%'



Code:
Update YourTable
Set    PostCode = Stuff(PostCode, Len(PostCode)-2, 0, ' ')
Where  PostCode Not Like '% ___%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thanks - worked a treat. I just made one minor change as seen below:

Code:
UPDATE Site
SET    postcode = STUFF(postcode, LEN(postcode)-2, 0, ' ')
WHERE  postcode NOT LIKE '% ___%'
AND (LEN(postcode) = 5 OR LEN(postcode) = 6 OR LEN(postcode) = 7)

Thanks!
 
Looking again, I may have made a slight mistake that you would probably not notice.

I think it should have been.

postcode not like '% ___'

without the last %.

Also.... I would have added the additional criteria like this...

Code:
UPDATE Site
SET    postcode = STUFF(postcode, LEN(postcode)-2, 0, ' ')
WHERE  postcode NOT LIKE '% ___'
AND    LEN(postcode) In (5,6,7)

The IN syntax is easier for me to read. [smile]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Ah yes - sorry, having one of those days today. Don't write SQL much any more these days, as you can tell.
Thanks for your help
Kate
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top