VB and VBA Users Source Code: UK Post Code Formatting for SQL Server SP
[
Home
|
Contents
|
Search
|
Reply
| Previous |
Next
]
VB/VBA Source Code
UK Post Code Formatting for SQL Server SP
By:
Clyde Weston
Email (spam proof):
Email the originator of this post
Date:
Tuesday, July 24, 2001
Hits:
737
Category:
Database/SQL/ADO
Article:
select left(corrected,datalength(corrected)-3) + ' ' + right(corrected,3) from (select case when nospaces like '[a-z][o0-9][a-z][o0-9][a-z][a-z]' then substring(nospaces,1,1) + replace(substring(nospaces,2,1),'o','0') + substring(nospaces,3,1) + replace(substring(nospaces,4,1),'o','0') + substring(nospaces,5,2) when nospaces like '[a-z][a-z][o0-9][o0-9][a-z][a-z]' then substring(nospaces,1,2) + replace(substring(nospaces,3,2),'o','0') + substring(nospaces,5,2) when nospaces like '[a-z][a-z][o0-9][a-z][o0-9][a-z][a-z]' then substring(nospaces,1,2) + replace(substring(nospaces,3,1),'o','0') + substring(nospaces,4,1) + replace(substring(nospaces,5,1),'o','0') + substring(nospaces,6,2) when nospaces like '[a-z][o0-9][o0-9][a-z][a-z]' then substring(nospaces,1,1) + replace(substring(nospaces,2,2),'o','0') + substring(nospaces,4,2) when nospaces like '[a-z][a-z][a-z^o][o0-9][a-z][a-z]' then substring(nospaces,1,3) + replace(substring(nospaces,4,1),'o','0') + substring(nospaces,5,2) when nospaces like '[a-z][1-9][o0-9][o0-9][a-z][a-z]' then substring(nospaces,1,2) + replace(substring(nospaces,3,2),'o','0') + substring(nospaces,5,2) when nospaces like '[a-z][a-z][1-9][o0-9][o0-9][a-z][a-z]' then substring(nospaces,1,3) + replace(substring(nospaces,4,2),'o','0') + substring(nospaces,6,2) else null end as corrected from (select ltrim(rtrim(UPPER(REPLACE(convert(varchar(8),[postcode]),' ','')))) nospaces from memberaddresses) part1 ) part2
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
