SQLで住所文字列を分割して新しいカラムを作成する方法
この記事では、SQLを用いて住所文字列から都道府県、市区町村、それ以降の部分を抽出し、新しいカラムを作成する方法を紹介します。
実はある例外を除いてほぼ完璧なクエリを最初に提示し、その後にその例外を含めた完全なクエリを紹介します。
エピソード
昔、とある会社に勤めていた頃、SQLを勉強している社員にこれを意地悪問題として出す面白い文化がありました。この問題は社員たちのSQLスキルを磨くための挑戦であり、ある特定の例外を見つけることができるかという柔軟性が鍵となっていました。
問題文
住所文字列を持つテーブルがあります。この住所文字列から「都道府県」「市区町村」「それ以降」の3つの新しいカラムを抽出して作成してください。
サンプルデータ
| id | full_address |
|---|---|
| 1 | 東京都千代田区丸の内1-1-1 |
| 2 | 大阪府大阪市北区梅田2-2-2 |
| 3 | 福岡県福岡市中央区天神3-3-3 |
| 4 | 三重県四日市市佐伯区1-1-1 |
| 5 | 北海道札幌市北区北24条西5丁目 |
| 6 | 愛知県名古屋市中区錦1-1-1 |
| 7 | 京都府京都市東山区清水2-2-2 |
| 8 | 広島県広島市中区基町3-3-3 |
| 9 | 宮城県仙台市青葉区中央4-4-4 |
| 10 | 神奈川県横浜市中区山下町5-5-5 |
初めのクエリ（99点）
まず、以下のクエリを試してみます。このクエリはほぼ完璧ですが、実はある例外に対応できていません。
SELECT
full_address,
CASE
WHEN full_address LIKE '東京都%' THEN '東京都'
WHEN full_address LIKE '大阪府%' THEN '大阪府'
WHEN full_address LIKE '北海道%' THEN '北海道'
ELSE SUBSTRING_INDEX(full_address, '県', 1) || '県'
END AS prefecture,
CASE
WHEN full_address LIKE '東京都%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '都', -1), '区', 1) || '区'
WHEN full_address LIKE '大阪府%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '府', -1), '市', 1) || '市'
WHEN full_address LIKE '北海道%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '道', -1), '市', 1) || '市'
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '県', -1), '市', 1) || '市'
END AS city,
CASE
WHEN full_address LIKE '東京都%' THEN SUBSTRING_INDEX(full_address, '区', -1)
WHEN full_address LIKE '大阪府%' THEN SUBSTRING_INDEX(full_address, '市', -1)
WHEN full_address LIKE '北海道%' THEN SUBSTRING_INDEX(full_address, '市', -1)
ELSE SUBSTRING_INDEX(full_address, '市', -1)
END AS rest_of_address
FROM addresses;
結果
| full_address | prefecture | city | rest_of_address |
|---------------------------------|------------|-----------|------------------|
| 東京都千代田区丸の内1-1-1 | 東京都 | 千代田区 | 丸の内1-1-1 |
| 大阪府大阪市北区梅田2-2-2 | 大阪府 | 大阪市 | 北区梅田2-2-2 |
| 福岡県福岡市中央区天神3-3-3 | 福岡県 | 福岡市 | 中央区天神3-3-3 |
| 三重県四日市市佐伯区1-1-1 | 三重県 | 日市市 | 佐伯区1-1-1 |
| 北海道札幌市北区北24条西5丁目 | 北海道 | 札幌市 | 北区北24条西5丁目|
| 愛知県名古屋市中区錦1-1-1 | 愛知県 | 名古屋市 | 中区錦1-1-1 |
| 京都府京都市東山区清水2-2-2 | 京都府 | 京都市 | 東山区清水2-2-2 |
| 広島県広島市中区基町3-3-3 | 広島県 | 広島市 | 中区基町3-3-3 |
| 宮城県仙台市青葉区中央4-4-4 | 宮城県 | 仙台市 | 青葉区中央4-4-4 |
| 神奈川県横浜市中区山下町5-5-5 | 神奈川県 | 横浜市 | 中区山下町5-5-5 |
クエリの問題点
このクエリは99点です。なぜなら、ある特定の例外に対応できていないからです。具体的には、三重県四日市市の「市」というワードが2回続くため、他の市と同じ抽出方法では正しく抽出できません。
| full_address | prefecture | city | rest_of_address |
|---------------------------------|------------|-----------|------------------|
| 東京都千代田区丸の内1-1-1 | 東京都 | 千代田区 | 丸の内1-1-1 |
| 大阪府大阪市北区梅田2-2-2 | 大阪府 | 大阪市 | 北区梅田2-2-2 |
| 福岡県福岡市中央区天神3-3-3 | 福岡県 | 福岡市 | 中央区天神3-3-3 |
| **三重県四日市市佐伯区1-1-1** | **三重県** | **日市市** | **佐伯区1-1-1** |
| 北海道札幌市北区北24条西5丁目 | 北海道 | 札幌市 | 北区北24条西5丁目|
| 愛知県名古屋市中区錦1-1-1 | 愛知県 | 名古屋市 | 中区錦1-1-1 |
| 京都府京都市東山区清水2-2-2 | 京都府 | 京都市 | 東山区清水2-2-2 |
| 広島県広島市中区基町3-3-3 | 広島県 | 広島市 | 中区基町3-3-3 |
| 宮城県仙台市青葉区中央4-4-4 | 宮城県 | 仙台市 | 青葉区中央4-4-4 |
| 神奈川県横浜市中区山下町5-5-5 | 神奈川県 | 横浜市 | 中区山下町5-5-5 |
完全なクエリ（100点）
では、この問題を修正した完全なクエリを見てみましょう。
SELECT
full_address,
CASE
WHEN full_address LIKE '東京都%' THEN '東京都'
WHEN full_address LIKE '大阪府%' THEN '大阪府'
WHEN full_address LIKE '北海道%' THEN '北海道'
WHEN full_address LIKE '三重県四日市市%' THEN '三重県'
ELSE SUBSTRING_INDEX(full_address, '県', 1) || '県'
END AS prefecture,
CASE
WHEN full_address LIKE '東京都%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '都', -1), '区', 1) || '区'
WHEN full_address LIKE '大阪府%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '府', -1), '市', 1) || '市'
WHEN full_address LIKE '北海道%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '道', -1), '市', 1) || '市'
WHEN full_address LIKE '三重県四日市市%' THEN '四日市市'
ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(full_address, '県
', -1), '市', 1) || '市'
END AS city,
CASE
WHEN full_address LIKE '東京都%' THEN SUBSTRING_INDEX(full_address, '区', -1)
WHEN full_address LIKE '大阪府%' THEN SUBSTRING_INDEX(full_address, '市', -1)
WHEN full_address LIKE '北海道%' THEN SUBSTRING_INDEX(full_address, '市', -1)
WHEN full_address LIKE '三重県四日市市%' THEN SUBSTRING_INDEX(full_address, '四日市市', -1)
ELSE SUBSTRING_INDEX(full_address, '市', -1)
END AS rest_of_address
FROM addresses;
結果
| full_address | prefecture | city | rest_of_address |
|---------------------------------|------------|-----------|------------------|
| 東京都千代田区丸の内1-1-1 | 東京都 | 千代田区 | 丸の内1-1-1 |
| 大阪府大阪市北区梅田2-2-2 | 大阪府 | 大阪市 | 北区梅田2-2-2 |
| 福岡県福岡市中央区天神3-3-3 | 福岡県 | 福岡市 | 中央区天神3-3-3 |
| 三重県四日市市佐伯区1-1-1 | 三重県 | 四日市市 | 佐伯区1-1-1 |
| 北海道札幌市北区北24条西5丁目 | 北海道 | 札幌市 | 北区北24条西5丁目|
| 愛知県名古屋市中区錦1-1-1 | 愛知県 | 名古屋市 | 中区錦1-1-1 |
| 京都府京都市東山区清水2-2-2 | 京都府 | 京都市 | 東山区清水2-2-2 |
| 広島県広島市中区基町3-3-3 | 広島県 | 広島市 | 中区基町3-3-3 |
| 宮城県仙台市青葉区中央4-4-4 | 宮城県 | 仙台市 | 青葉区中央4-4-4 |
| 神奈川県横浜市中区山下町5-5-5 | 神奈川県 | 横浜市 | 中区山下町5-5-5 |
まとめ
この記事では、SQLを用いて住所文字列から都道府県、市区町村、それ以降の部分を抽出し、新しいカラムを作成する方法を紹介しました。特に、三重県四日市市のような例外を考慮することで、正確なデータ抽出が可能となることを示しました。
実務では、住所データのような複雑な文字列操作が求められることが多々あります。今回の例題を通じて、SQLの柔軟性と強力さを再確認するとともに、データ処理の際には細心の注意が必要であることを学ぶことができました。
皆さんも、自身のプロジェクトで同様の問題に直面した際には、このアプローチを参考にしてみてください。