Extract UK Postcode from an address string

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 2012 Forums

Transact-SQL (2012)

Extract UK Postcode from an address string

Author

Topic

steveajones45
Starting Member

2 Posts

Posted - 2014-04-07 : 07:09:50

Good Afternoon,

I am trying to extract just the postcode from an address string, an example of the string is:

123 The Street Bracknell Berkshire RG12 1AA

I just want to return RG12 1AA in my result set.

Thank you in advance.
Steve

steveajones45
Starting Member

2 Posts

Posted - 2014-04-07 : 08:18:07

I have found the solution....


SELECT LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(RTRIM('123 The Street Bracknell Berkshire RG12 1AA')), 1, CHARINDEX(' ',REVERSE(RTRIM('123 The Street Bracknell Berkshire RG12 1AA')) , CHARINDEX(' ',  REVERSE(RTRIM('123 The Street Bracknell Berkshire RG12 1AA')))+1)))))

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-07 : 17:37:00

DECLARE @Text VARCHAR(100) = '123 The Street Bracknell Berkshire RG12 1AA'


SELECT REPLACE(REVERSE(LEFT(REPLACE(REVERSE(@Text),' ', '.'), CHARINDEX('.', REPLACE(REVERSE(@Text),' ', '.'), CHARINDEX('.', REPLACE(REVERSE(@Text),' ', '.'), 0)+1)-1)), '.', ' ')

I replaced an empty space to . and then a . to an empty space. You can eliminate that step and look for an empty space directly to make it a little faster.

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-04-08 : 07:46:45

Will this work too?

declare @text varchar(200)
set @text='123 The Street Bracknell Berkshire RG12 1AA'
select right(@text,charindex(' ',reverse(@text))+4) as zip_code

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-04-08 : 08:12:33

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

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources