Nettoyage des codes postaux des adresses britanniques dans SQL Server 2008
Créez une fonction SQL Server pour nettoyer, remplacer les erreurs courantes et reformater les codes postaux britanniques avec la position d'espace correcte
Le contexte
Cet article a été très populaire, je l'ai donc mis à jour avec plus d'informations et nettoyé le formatage, car quelques espaces ont été manqués, afin qu'il puisse être copié directement dans SSMS maintenant.
Il utilise les étapes suivantes;
Il utilise les étapes suivantes;
- Supprimer tout de la chaîne de texte (y compris les espaces) à l'exception des chiffres et des lettres
- Remplace les erreurs de frappe standard telles que;
- Portsmouth est PO, Colchester est CO, mais est parfois entré comme P0 ou C0 (un zéro)
- Ilford et Reading sont IG et RG, mais parfois être inscrits avec un 6 à la place du G
- Déterminez le format dans lequel le code postal doit être et insérez un espace là où il devrait être.
- Vérifiez que le format est correct et renvoyez une nouvelle valeur si vrai, sinon renvoyez l'ancien format.
Veuillez noter que cela ne vérifie pas si le code postal existe réellement, et des lectures complémentaires peuvent être trouvées sur le lien suivant Codes postaux au Royaume-Uni .
SQL
CREATE FUNCTION CleansePostCode (@PostCode VARCHAR(100)) RETURNS VARCHAR(100) AS BEGINDECLARE @OldPostCode VARCHAR(100)SET @OldPostCode=LTRIM(RTRIM(@PostCode))
--Clean to Numbers and LettersDECLARE @Letter INTSET @Letter = PATINDEX('%[^0-Z]%',@PostCode)BEGINWHILE @Letter>0BEGINSET @PostCode = STUFF(@PostCode,@Letter,1,'')SET @Letter = PATINDEX('%[^0-Z]%',@PostCode)ENDEND
--Replace obvious errorsSET @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 spaceSET @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 1WHEN @PostCode LIKE '[A-Z][0-9] [0-9][A-Z][A-Z]' THEN 2WHEN @PostCode LIKE '[A-Z][0-9][0-9] [0-9][A-Z][A-Z]' THEN 3WHEN @PostCode LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]'THEN 4WHEN @PostCode LIKE '[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]' THEN 5WHEN @PostCode LIKE '[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]' THEN 6ELSE 0 END)=0BEGIN SET @NewPostCode=@OldPostCodeENDELSESET @NewPostCode=@PostCodeRETURN @NewPostCodeEND