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)))))
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -