SQL Server2008での英国の住所郵便番号のクレンジング
SQL Server関数を作成して、よくある間違いをクレンジングし、置き換え、英国の郵便番号を正しいスペース位置に再フォーマットします
環境
この記事は非常に人気があり、いくつかのスペースが欠落しているため、いくつかの情報を追加してフォーマットをクリーンアップし、SSMSに直接コピーできるようにしました。
次の手順を使用します。
次の手順を使用します。
- 数字と文字を除いて、テキスト文字列（スペースを含む）からすべてを削除します
- 次のような標準的な入力ミスを置き換えます。
- ポーツマスはPO、コルチェスターはCOですが、P0またはC0（ゼロ）として入力されることもあります。
- イルフォードとレディングはIGとRGですが、Gの代わりに6を入力することもあります
- 郵便番号が含まれるべきフォーマットを計算し、あるべき場所にスペースを挿入します。
- 形式が正しいことを確認し、trueの場合は新しい値を返し、そうでない場合は古い形式を返します。
これは郵便番号が実際に存在するかどうかをチェックしないことに注意してください。詳細については、次のリンク「英国の郵便番号」を参照してください。
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