SQL CASEを使用して郵便番号から都道府県を判定する

データベースに顧客情報を保存している場合、
最近のWEBアプリ等ではデータベースにも都道府県専用のフィールドが用意されていて、
入力時には郵便番号から自動的に都道府県の情報が保存されるようになっているようなアプリが中心となっています。

しかし、レガシーな業務用アプリケーションなどでは、都道府県専用のフィールドなど無く、
例えば住所１～住所３に分割してはいても、住所１のフィールドに保存された内容を確認すると
都道府県がきちんと入力されているのもあれば、市や郡からしか入力されていないものもあり、
例えば都道府県別に顧客や売上の分布を分析したいというような要求が出てきた場合に
なかなか一筋縄ではいかないケースが多いかと思います。

かと言って、アプリ自体を都道府県対応に更新するのは非常にコストがかかる事が予想されます。

今回は、とりあえず集計実行時に保存された郵便番号の値から都道府県を引っ張り出す方法を紹介してみたいと思います。

今回参考にさせていただいたのは以下の記事です

郵便番号と都道府県の対応表が欲しい

郵便番号の先頭２ケタで一部例外はありますが、都道府県が判定できるようです。
（対応表の部分を最後に抜き出して記載してます）

これをもとにして、SQLで郵便番号から都道府県を判定するSQLの例を以下に示します。

※　テーブル名を「tableA」、郵便番号を保存するフィールド名を 「zipcode」、
　　 データ型は　varchar 、使用するデータベースは 「SQLSERVER 2008」　とします

以下のようなSQL文になるかと思います

select
(case when cast(substring(zipcode , 1 , 2) as int) between 10 and 20 then '東京都'
        when cast(substring(zipcode , 1 , 2) as int) between 21 and 25 then '神奈川県'
        when cast(substring(zipcode , 1 , 2) as int) between 26 and 29 then '千葉県'
        when cast(substring(zipcode , 1 , 2) as int) between 30 and 31 then '茨城県'
        when cast(substring(zipcode , 1 , 2) as int) = 32 then '栃木県'
        when cast(substring(zipcode , 1 , 2) as int) between 33 and 36 then '埼玉県'
        when cast(substring(zipcode , 1 , 2) as int) = 37 then '群馬県'
        when cast(substring(zipcode , 1 , 2) as int) between 38 and 39 then '長野県'
        when cast(substring(zipcode , 1 , 2) as int) = 40 then '山梨県'
        when cast(substring(zipcode , 1 , 2) as int) between 41 and 43 then '静岡県'
        when cast(substring(zipcode , 1 , 2) as int) between 44 and 49 then '愛知県'
        when cast(substring(zipcode , 1 , 2) as int) = 50 then '岐阜県'
        when cast(substring(zipcode , 1 , 2) as int) = 51 then '三重県'
        when cast(substring(zipcode , 1 , 2) as int) = 52 then '滋賀県'
        when cast(substring(zipcode , 1 , 2) as int) between 53 and 59 then '大阪府'
        when cast(substring(zipcode , 1 , 2) as int) between 60 and 62 then '京都府'
        when cast(substring(zipcode , 1 , 2) as int) = 63 then '奈良県'
        when cast(substring(zipcode , 1 , 2) as int) = 64 then '和歌山県'
        when cast(substring(zipcode , 1 , 2) as int) between 65 and 67 then '兵庫県'
        when cast(substring(zipcode , 1 , 2) as int) = 68 then '鳥取県'
        when cast(substring(zipcode , 1 , 2) as int) = 69 then '島根県'
        when cast(substring(zipcode , 1 , 2) as int) between 70 and 71 then '岡山県'
        when cast(substring(zipcode , 1 , 2) as int) between 72 and 73 then '広島県'
        when cast(substring(zipcode , 1 , 2) as int) between 74 and 75 then '山口県'
        when cast(substring(zipcode , 1 , 2) as int) = 76 then '香川県'
        when cast(substring(zipcode , 1 , 2) as int) = 77 then '徳島県'
        when cast(substring(zipcode , 1 , 2) as int) = 78 then '高知県'
        when cast(substring(zipcode , 1 , 2) as int) = 79 then '愛媛県'
        when cast(substring(zipcode , 1 , 2) as int) between 80 and 83 then '福岡県'
        when cast(substring(zipcode , 1 , 2) as int) = 84 then '佐賀県'
        when cast(substring(zipcode , 1 , 2) as int) = 85 then '長崎県'
        when cast(substring(zipcode , 1 , 2) as int) = 86 then '熊本県'
        when cast(substring(zipcode , 1 , 2) as int) = 87 then '大分県'
        when cast(substring(zipcode , 1 , 2) as int) = 88 then '宮崎県'
        when cast(substring(zipcode , 1 , 2) as int) = 89 then '鹿児島県'
        when cast(substring(zipcode , 1 , 2) as int) = 90 then '沖縄県'
        when cast(substring(zipcode , 1 , 2) as int) = 91 then '福井県'
        when cast(substring(zipcode , 1 , 2) as int) = 92 then '石川県'
        when cast(substring(zipcode , 1 , 2) as int) = 93 then '富山県'
        when cast(substring(zipcode , 1 , 2) as int) between 94 and 95 then '新潟県'
        when cast(substring(zipcode , 1 , 2) as int) between 96 and 97 then '福島県'
        when cast(substring(zipcode , 1 , 2) as int) = 98 then '宮城県'
        when cast(substring(zipcode , 1 , 2) as int) = 99 then '山形県'
        when cast(substring(zipcode , 1 , 2) as int) = 0 then '北海道'
        when cast(substring(zipcode , 1 , 2) as int) between 4 and 9 then '北海道'
        when cast(substring(zipcode , 1 , 2) as int) = 1 then '秋田県'
        when cast(substring(zipcode , 1 , 2) as int) = 2 then '岩手県'
        when cast(substring(zipcode , 1 , 2) as int) = 3 then '青森県'
 end
) 都道府県
from tableA

キャスト関数を使用せず、文字列のままでも between で範囲指定できるとは思いますが
念のため数値に変換して判定しています。

また、SUBSTRING 関数は  SUBSTRING(string, start, length)　と定義されていますが
start で指定する先頭の位置は 1から始まるので念のため

CASE はSQLの標準なのでどのデータベースでも共通で使えると思いますが
SUBSTRING関数は各データベースで微妙に異なるので、詳しくは こちら を参照してください。

まぁ、どの程度使用する機会があるかはわかりませんが、参考になれば幸いです。


10～20　東京都
21～25　神奈川県
26～29　千葉県
30・31　茨城県
32　　　栃木県
33～36　埼玉県
37　　　群馬県
38・39　長野県
40　　　山梨県
41～43　静岡県
44～49　愛知県
50　　　岐阜県
51　　　三重県
52　　　滋賀県
53～59　大阪府
60～62　京都府
63　　　奈良県
64　　　和歌山県
65～67　兵庫県
68　　　鳥取県
69　　　島根県
70・71　岡山県
72・73　広島県
74・75　山口県
76　　　香川県
77　　　徳島県
78　　　高知県
79　　　愛媛県
80～83　福岡県
84　　　佐賀県
85　　　長崎県
86　　　熊本県
87　　　大分県
88　　　宮崎県
89　　　鹿児島県
90　　　沖縄県
91　　　福井県
92　　　石川県
93　　　富山県
94・95　新潟県
96・97　福島県
98　　　宮城県
99　　　山形県
00・04～09　北海道
01　　　秋田県
02　　　岩手県
03　　　青森県