UK Postcodes - 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

SQL Server 2005 Forums

Transact-SQL (2005)

UK Postcodes

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-05-29 : 07:05:30

Hi, could anyone assist/direct how to write a query/function to retrieve the first part of the postcode?

For instance: W2 3LP & NW3 4LJ
I am only interested in W" and NW3

Thanks

Cipriani

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 07:08:13

select left('w2 3lp',charindex(' ','w2 3lp'))

select substring('w2 3lp',1,charindex(' ','w2 3lp'))

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-05-29 : 07:11:12

[code]SELECT LEFT(ZIP, CHARINDEX(' ', Zip)-1), CHARINDEX(' ', Zip)
FROM (
SELECT Zip = 'W2 3LP' UNION ALL SELECT 'NW3 4LJ') AS a[/code]

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 07:13:08

For other people seeing this, here are two algorithm to validate and extract UK postcode

http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Validate-UK-postcode.aspx
http://weblogs.sqlteam.com/peterl/archive/2008/08/13/Extract-UK-postcode.aspx

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-05-29 : 07:19:13

Superb, thank you!

quote:
Originally posted by bklr

select left('w2 3lp',charindex(' ','w2 3lp'))

select substring('w2 3lp',1,charindex(' ','w2 3lp'))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-29 : 07:45:13

quote:
Originally posted by cipriani1984

Superb, thank you!

quote:
Originally posted by bklr

select left('w2 3lp',charindex(' ','w2 3lp'))

select substring('w2 3lp',1,charindex(' ','w2 3lp'))

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources