UI設計したり、ウェブシステムを開発したりします。 

SQL見るだけクエリ#040 MySQL 正規表現を使って検索する方法 REGEXP

記入日:2023-02-17 編集日:2023-02-17

SQL見るだけクエリ。今回は全国12万件の郵便番号データのテーブルから、
郵便番号の下4桁が0001で且つcityカラムが町で終わるレコードを取得してみようと思います。
正規表現を使って検索する必要がありますので REGEXP演算子を活用しようと思います。

この記事を読むと(約5分)
12万件程度のデータから、正規表現を使って指定の文字を含む物を取得する方法を知る事ができます。

postal_codeというテーブルを準備しています。
このテーブルには全国12万件の郵便番号情報が保存されています。

postal7カラムには、郵便番号の7桁
cityカラムには、市区町村の文字が保存されています。

さていつもの様に、テーブルの情報を取得するSQLに関して、注目する部分は、以下の9~10行目のみです。

SELECT postal7, pref, city, address FROM postal_code
WHERE postal7 REGEXP "001$" AND city REGEXP "町$"

この部分で、郵便番号の下4桁が0001で且つcityカラムが町で終わるレコードを取得しています。

REGEXP演算子を使って、

// postal7カラムの中で文字列の末尾に 001 を含むもの
postal7 REGEXP "0001$"

// cityカラムの中で文字列の末尾に 町 を含むもの
city REGEXP "町$"

を取得条件(WHERE )にしております。

ちなみに
文字列の先頭に一致する文字を指定する場合は $の代わりに^を使う事になります。

try {
    include_once '../model/class.php';

    // データベース接続
    $database = new Database();
    $db = $database->connect();

    // SQLを準備し、パラメータをバインドする SELECT * FROM tab1 WHERE emp_name REGEXP 'Ki$';
    $query = 'SELECT postal7, pref, city, address FROM postal_code 
    WHERE postal7 REGEXP "0001$" AND city REGEXP "町$"';
    $stmt = $db->prepare($query);

    // 実行する
    $stmt->execute();

    // 結果を取得する
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    print_r($result);

    // データベースから切断する
    $database->disconnect();

} catch(PDOException $e) {
    echo $e->getMessage();
}

これを実行すると、二次元連想配列で、取得することができます。

            Array
(
    [0] => Array
        (
            [postal7] => 0440001
            [pref] => 北海道
            [city] => 虻田郡倶知安町
            [address] => 北一条東
        )

    [1] => Array
        (
            [postal7] => 0450001
            [pref] => 北海道
            [city] => 岩内郡岩内町
            [address] => 大浜
        )

    [2] => Array
        (
            [postal7] => 0460001
            [pref] => 北海道
            [city] => 余市郡余市町
            [address] => 栄町
        )

    [3] => Array
        (
            [postal7] => 0920001
            [pref] => 北海道
            [city] => 網走郡美幌町
            [address] => 高野
        )

    [4] => Array
        (
            [postal7] => 0540001
            [pref] => 北海道
            [city] => 勇払郡むかわ町
            [address] => 旭岡
        )

    [5] => Array
        (
            [postal7] => 0550001
            [pref] => 北海道
            [city] => 沙流郡日高町
            [address] => 富川北
        )

    [6] => Array
        (
            [postal7] => 0570001
            [pref] => 北海道
            [city] => 浦河郡浦河町
            [address] => 東幌別(1〜131番地)
        )

    [7] => Array
        (
            [postal7] => 0580001
            [pref] => 北海道
            [city] => 様似郡様似町
            [address] => 旭
        )

    [8] => Array
        (
            [postal7] => 0560001
            [pref] => 北海道
            [city] => 日高郡新ひだか町
            [address] => 静内目名(その他)
        )

    [9] => Array
        (
            [postal7] => 0810001
            [pref] => 北海道
            [city] => 上川郡新得町
            [address] => 一条北
        )

    [10] => Array
        (
            [postal7] => 0820001
            [pref] => 北海道
            [city] => 河西郡芽室町
            [address] => 平和
        )

    [11] => Array
        (
            [postal7] => 0830001
            [pref] => 北海道
            [city] => 中川郡池田町
            [address] => 旭町
        )

    [12] => Array
        (
            [postal7] => 9870001
            [pref] => 宮城県
            [city] => 遠田郡美里町
            [address] => 藤ケ崎
        )

    [13] => Array
        (
            [postal7] => 9670001
            [pref] => 福島県
            [city] => 南会津郡南会津町
            [address] => 長野
        )

    [14] => Array
        (
            [postal7] => 9680001
            [pref] => 福島県
            [city] => 大沼郡金山町
            [address] => 水沼
        )

    [15] => Array
        (
            [postal7] => 3250001
            [pref] => 栃木県
            [city] => 那須郡那須町
            [address] => 高久甲(その他)
        )

    [16] => Array
        (
            [postal7] => 3450001
            [pref] => 埼玉県
            [city] => 北葛飾郡杉戸町
            [address] => 木津内
        )

    [17] => Array
        (
            [postal7] => 2550001
            [pref] => 神奈川県
            [city] => 中郡大磯町
            [address] => 高麗
        )

    [18] => Array
        (
            [postal7] => 2580001
            [pref] => 神奈川県
            [city] => 足柄上郡松田町
            [address] => 寄
        )

    [19] => Array
        (
            [postal7] => 9270001
            [pref] => 石川県
            [city] => 鳳珠郡穴水町
            [address] => 樟谷
        )

    [20] => Array
        (
            [postal7] => 3930001
            [pref] => 長野県
            [city] => 諏訪郡下諏訪町
            [address] => 樋橋
        )

    [21] => Array
        (
            [postal7] => 3970001
            [pref] => 長野県
            [city] => 木曽郡木曽町
            [address] => 福島
        )

    [22] => Array
        (
            [postal7] => 4110001
            [pref] => 静岡県
            [city] => 田方郡函南町
            [address] => 桑原(茨ケ平)
        )

    [23] => Array
        (
            [postal7] => 4110001
            [pref] => 静岡県
            [city] => 田方郡函南町
            [address] => 桑原(箱根峠)
        )

    [24] => Array
        (
            [postal7] => 6180001
            [pref] => 大阪府
            [city] => 三島郡島本町
            [address] => 山崎
        )

    [25] => Array
        (
            [postal7] => 5850001
            [pref] => 大阪府
            [city] => 南河内郡河南町
            [address] => 東山
        )

    [26] => Array
        (
            [postal7] => 6360001
            [pref] => 奈良県
            [city] => 北葛城郡王寺町
            [address] => 舟戸
        )

    [27] => Array
        (
            [postal7] => 6380001
            [pref] => 奈良県
            [city] => 吉野郡下市町
            [address] => 阿知賀
        )

    [28] => Array
        (
            [postal7] => 6430001
            [pref] => 和歌山県
            [city] => 有田郡湯浅町
            [address] => 山田
        )

    [29] => Array
        (
            [postal7] => 6450001
            [pref] => 和歌山県
            [city] => 日高郡みなべ町
            [address] => 東吉田
        )

    [30] => Array
        (
            [postal7] => 6810001
            [pref] => 鳥取県
            [city] => 岩美郡岩美町
            [address] => 牧谷
        )

    [31] => Array
        (
            [postal7] => 6960001
            [pref] => 島根県
            [city] => 邑智郡川本町
            [address] => 川本
        )

    [32] => Array
        (
            [postal7] => 6850001
            [pref] => 島根県
            [city] => 隠岐郡隠岐の島町
            [address] => 大久
        )

    [33] => Array
        (
            [postal7] => 7350001
            [pref] => 広島県
            [city] => 安芸郡府中町
            [address] => 桜ケ丘
        )

    [34] => Array
        (
            [postal7] => 7360001
            [pref] => 広島県
            [city] => 安芸郡海田町
            [address] => 砂走
        )

    [35] => Array
        (
            [postal7] => 7750001
            [pref] => 徳島県
            [city] => 海部郡牟岐町
            [address] => 河内
        )

    [36] => Array
        (
            [postal7] => 7660001
            [pref] => 香川県
            [city] => 仲多度郡琴平町
            [address] => 琴平町の次に427番地以降がくる場合(川西)
        )

    [37] => Array
        (
            [postal7] => 7640001
            [pref] => 香川県
            [city] => 仲多度郡多度津町
            [address] => 東新町
        )

    [38] => Array
        (
            [postal7] => 7860001
            [pref] => 高知県
            [city] => 高岡郡四万十町
            [address] => 藤ノ川
        )

    [39] => Array
        (
            [postal7] => 8070001
            [pref] => 福岡県
            [city] => 遠賀郡水巻町
            [address] => 猪熊
        )

    [40] => Array
        (
            [postal7] => 8270001
            [pref] => 福岡県
            [city] => 田川郡川崎町
            [address] => 安眞木
        )

    [41] => Array
        (
            [postal7] => 8440001
            [pref] => 佐賀県
            [city] => 西松浦郡有田町
            [address] => 泉山
        )

    [42] => Array
        (
            [postal7] => 8840001
            [pref] => 宮崎県
            [city] => 児湯郡高鍋町
            [address] => 高鍋町
        )

)
			

郵便番号の下4桁が0001で且つ
cityカラムが町で終わるレコードを取得

postal7 pref city address
0440001
北海道
虻田郡倶知安町
北一条東
0450001
北海道
岩内郡岩内町
大浜
0460001
北海道
余市郡余市町
栄町
0920001
北海道
網走郡美幌町
高野
0540001
北海道
勇払郡むかわ町
旭岡
0550001
北海道
沙流郡日高町
富川北
0570001
北海道
浦河郡浦河町
東幌別(1〜131番地)
0580001
北海道
様似郡様似町
0560001
北海道
日高郡新ひだか町
静内目名(その他)
0810001
北海道
上川郡新得町
一条北
0820001
北海道
河西郡芽室町
平和
0830001
北海道
中川郡池田町
旭町
9870001
宮城県
遠田郡美里町
藤ケ崎
9670001
福島県
南会津郡南会津町
長野
9680001
福島県
大沼郡金山町
水沼
3250001
栃木県
那須郡那須町
高久甲(その他)
3450001
埼玉県
北葛飾郡杉戸町
木津内
2550001
神奈川県
中郡大磯町
高麗
2580001
神奈川県
足柄上郡松田町
9270001
石川県
鳳珠郡穴水町
樟谷
3930001
長野県
諏訪郡下諏訪町
樋橋
3970001
長野県
木曽郡木曽町
福島
4110001
静岡県
田方郡函南町
桑原(茨ケ平)
4110001
静岡県
田方郡函南町
桑原(箱根峠)
6180001
大阪府
三島郡島本町
山崎
5850001
大阪府
南河内郡河南町
東山
6360001
奈良県
北葛城郡王寺町
舟戸
6380001
奈良県
吉野郡下市町
阿知賀
6430001
和歌山県
有田郡湯浅町
山田
6450001
和歌山県
日高郡みなべ町
東吉田
6810001
鳥取県
岩美郡岩美町
牧谷
6960001
島根県
邑智郡川本町
川本
6850001
島根県
隠岐郡隠岐の島町
大久
7350001
広島県
安芸郡府中町
桜ケ丘
7360001
広島県
安芸郡海田町
砂走
7750001
徳島県
海部郡牟岐町
河内
7660001
香川県
仲多度郡琴平町
琴平町の次に427番地以降がくる場合(川西)
7640001
香川県
仲多度郡多度津町
東新町
7860001
高知県
高岡郡四万十町
藤ノ川
8070001
福岡県
遠賀郡水巻町
猪熊
8270001
福岡県
田川郡川崎町
安眞木
8440001
佐賀県
西松浦郡有田町
泉山
8840001
宮崎県
児湯郡高鍋町
高鍋町

SQLの使いどころ

サイトに大量の情報を掲載することは、ユーザーにとってとても便利なことです。しかし、情報が多すぎると、目的の情報を見つけるのが困難になります。そのため、SQLを使用することで、情報を効率的に管理することができます。

SQLを使用することで、データベース内の情報を検索したり、フィルタリングしたりすることができます。これにより、ユーザーは目的の情報をスムーズかつ簡単に見つけることができます。

また、SQLを使用することで、データを集計したり、統合したりすることもできます。これにより、ユーザーはさまざまな視点から情報を分析することができます。つまり、サイトに多くの情報を掲載することが重要である一方で、その情報を効率的かつ簡単に管理することが求められる場合には、SQLが不可欠であるといえます。

KK

機械工学を専攻。工業デザイナーとして、国内及び海外の自動車・搬送ラインの設計などに従事した後、2003年にウェブシステム会社を設立。UI設計やウェブシステムの開発、DX化のディレクションなどを行っています。