英国卸妆地址邮政编码SQL Server 2008中

创建一个函数在SQL Server中清洗和重新格式化英国的邮政编码

这篇文章已经相当普及，所以我有一些更多的信息更新，并清理格式，一对夫妇的空间被错过了，所以它可以直接现在可以复制到SSMS。

它使用以下步骤：

从数字和字母删除一切从文本字符串（包括空格）分开
取代了标准的输入错误等;
朴茨茅斯是PO，科尔切斯特是CO，但有时候会输入为P0或C0（零）
伊尔福德和阅读是IG和RG，但有时得到的地方G的6进入
制定格式thepostcode应该是，然后插入它应该有一个空格。
检查格式是否正确，并返回一个新的值如果为true，否则返回旧格式。

请注意，这并不检查后代码是否实际存在，并进一步阅读可以在以下链接找到英国邮区编号。

SQL

CREATE FUNCTION CleansePostCode (@PostCode VARCHAR(100))
RETURNS VARCHAR(100)
AS BEGIN

DECLARE @OldPostCode VARCHAR(100)
SET @OldPostCode=LTRIM(RTRIM(@PostCode))

--Clean to Numbers and Letters
DECLARE @Letter INT
   SET @Letter = PATINDEX('%[^0-Z]%',@PostCode)
   BEGIN
   WHILE @Letter>0
   BEGIN
   SET @PostCode = STUFF(@PostCode,@Letter,1,'')
   SET @Letter = PATINDEX('%[^0-Z]%',@PostCode)
   END
END

--Replace obvious errors
SET @PostCode =(CASE WHEN LEFT(@PostCode,2)='P0' THEN STUFF(@PostCode,1,2,'PO')
WHEN LEFT(@PostCode,2)='C0' THEN STUFF(@PostCode,1,2,'CO')
WHEN LEFT(@PostCode,2)='I6' THEN STUFF(@PostCode,1,2,'IG')
WHEN LEFT(@PostCode,2)='HO' THEN STUFF(@PostCode,1,2,'HD')
WHEN LEFT(@PostCode,2)='C8' THEN STUFF(@PostCode,1,2,'CB')
WHEN LEFT(@PostCode,2)='D0' THEN STUFF(@PostCode,1,2,'DO')
WHEN LEFT(@PostCode,2)='H5' THEN STUFF(@PostCode,1,2,'HS')
WHEN LEFT(@PostCode,2)='0L' THEN STUFF(@PostCode,1,2,'OL')
WHEN LEFT(@PostCode,2)='0X ' THEN STUFF(@PostCode,1,2,'OX')
WHEN LEFT(@PostCode,2)='P0' THEN STUFF(@PostCode,1,2,'PO')
WHEN LEFT(@PostCode,2)='R6' THEN STUFF(@PostCode,1,2,'RG')
ELSE @PostCode END)

--Works out correct pattern and insert space
SET @PostCode = (CASE WHEN @PostCode LIKE '[A-Z][A-Z][0-9][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,3)+' '+RIGHT(@PostCode,3)
WHEN @PostCode LIKE '[A-Z][0-9][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,2)+' '+RIGHT(@PostCode,3)
WHEN @PostCode LIKE '[A-Z][0-9][0-9][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,3)+' '+RIGHT(@PostCode,3)
WHEN @PostCode LIKE '[A-Z][0-9][A-Z][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,3)+' '+RIGHT(@PostCode,3)
WHEN @PostCode LIKE '[A-Z][A-Z][0-9][0-9][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,4)+' '+RIGHT(@PostCode,3)
WHEN @PostCode LIKE '[A-Z][A-Z][0-9][A-Z][0-9][A-Z][A-Z]' THEN LEFT(@PostCode,4)+' '+RIGHT(@PostCode,3)
ELSE @PostCode END)

--Update @NewPostCode to old value if pattern does not match, or else use new value.
DECLARE @NewPostCode VARCHAR(100)
IF (CASE WHEN @PostCode LIKE '[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]' THEN 1
WHEN @PostCode LIKE '[A-Z][0-9] [0-9][A-Z][A-Z]' THEN 2
WHEN @PostCode LIKE '[A-Z][0-9][0-9] [0-9][A-Z][A-Z]' THEN 3
WHEN @PostCode LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'THEN 4
WHEN @PostCode LIKE '[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]' THEN 5
WHEN @PostCode LIKE '[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]' THEN 6
ELSE 0 END)=0
BEGIN
   SET @NewPostCode=@OldPostCode
END
ELSE
   SET @NewPostCode=@PostCode
RETURN @NewPostCode
END