Inserting a space in a postcode

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 Programming

Inserting a space in a postcode

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-30 : 11:50:07

We have many postcodes in our database that are UK ones. Some cutomers enter then as one string. Example BA124XT

In the SQL table we have two columns PostCode and Country. Is it possible to write a SQL script that will insert a space three characters from the right of the postcode where the country is UK?

So before it would be BA124XT and after BA12 4XT for them all?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-30 : 11:58:48

You certainly can. However, I'd suggest that this is a formatting issue and best left to a front-end or other place and not in a SQL Server.

But, if you want to here are a couple of ways to do it:

DECLARE @Foo VARCHAR(20) = 'BA124XT';

SELECT
	LEFT(@Foo, 4) + CHAR(32) + RIGHT(@Foo, 3),
	STUFF(@Foo, 5, 0, CHAR(32))

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-30 : 12:04:05

Thanks. When I said all of them I meant all postcodes that don't contain a space? I am sure that it is not wise to run this via SQL but thought that it maybe possible somehow. We only have one of the postcodes above.

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-30 : 12:50:01

I have this SQL that finds all those postcodes that are wrong but now I need to put a space three characters from the right. Any ideas?

select FirstName,LastName,Email,Zip
from Address
where len(Zip) in (6,7) and len(Zip) = len(replace(Zip, ' ', ''))
and Country = 'United Kingdom’

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-30 : 13:06:57

Assuming that select statement gets you the correct rows, you can just turn it into an UPDATE:

UPDATE 
	Address
SET
	Zip = STUFF(Zip, 5, 0, CHAR(32))
where 
	len(Zip) in (6,7) and len(Zip) = len(replace(Zip, ' ', ''))
	and Country = 'United Kingdom’

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-31 : 07:06:15

Perfect! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources