Clean up postcodes 1

[katehol]

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

 

[gmmastros]

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

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

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

 

[katehol]

Thanks - worked a treat. I just made one minor change as seen below:

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!

 

[gmmastros]

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

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.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[katehol]

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