SQL rule for correct UK Post Codes

You may need to correct UK Postal Codes at T-SQL level. The following has been developed from the full Royal Mail post code rules book:

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

Location Map

Updates

Postcodes

Postcodes

SQL rule for correct UK Post Codes

Copyright DBU 2000. Last updated 24 May 2004

Copyright DBU 2000.
Last updated 24 May 2004