La pulizia del Regno Unito Indirizzo NPA in SQL Server 2008

Creare una funzione per pulire e ri-formato del Regno Unito NPA in SQL Server

Questo articolo è stato molto popolare, così ho aggiornato con qualche informazione in più e ripulito la formattazione, come un paio di spazi sono stati perso, in modo che possa essere copiato direttamente in SSMS ora.

Esso utilizza le seguenti operazioni;

Togliere tutto dal stringa di testo (spazi inclusi) oltre a numeri e lettere
Sostituisce gli errori di battitura standard come;
Portsmouth è PO, Colchester è CO, ma a volte viene immesso come P0 o C0 (uno zero)
Ilford e Reading sono IG e RG, ma a volte vengono inseriti con un 6 al posto del G
Allenatevi formato thepostcode dovrebbe essere in, e inserire uno spazio dove dovrebbe essere.
Controllare che il formato sia corretto e restituire un nuovo valore se vero, altrimenti restituire il vecchio formato.

Si prega di notare, questo non verifica se il codice postale esiste realmente, e ulteriori letture si possono trovare al seguente link Codici postali nel Regno Unito .

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