MySQLで正規表現を使って検索する ～REGEXP（文字列＋数値、電話番号、メールアドレス）

MySQLのREGEXPを使えば、WHERE句で正規表現の検索が可能です。

正規表現を知っていれば、文字列のカラムに対して範囲指定検索なんてことも可能なり、色々な場面で利用できて便利なので、ここで使い方を整理しておきましょう。

使い方

使い方としてはいたって簡単で、WHERE句に以下の構文を書くだけです。

SELECT * FROM table WHERE [カラム名] REGEXP [正規表現の条件];

 上記の通りカラム名の後に「 REGEXP」を記述し、条件を指定すれば使えます。

ただ自分のやりたいことを実現するには正規表現の知識が必要になります。

今回は正規表現の詳しい内容は省きますが、自分がMySQLで正規表現の検索をする際、よく使うサンプルをいくつか提示する形で紹介したいと思います。

正規表現を使って「文字列＋数値」のカラムに対して範囲検索する

 データベース「table」に対して、カラム「code」に「文字列＋数値」の値が入っているとします。

具体的には「CODE_1」から「CODE_2000000」まで値が、計200万件登録されています。

/* 【10万ぐ切りで検索】 */
-- ヒットする範囲：CODE_1 ~ CODE_100000
SELECT COUNT(*) FROM table WHERE code REGEXP '^CODE_[0-9]{1,5}$' OR code = 'CODE_100000';
/* [正規表現のポイント]
   ^：先頭の文字であることを表す
   $：末尾であることを表す 
    [0-9]{1,5}：1文字以上5文字以下の数値
    ⇒　つまり1~99999の値　 */

-- ヒットする範囲：CODE_100001 ~ CODE_200000
SELECT COUNT(*) FROM table WHERE (code REGEXP  '^CODE_1[0-9]{5}$' OR code = 'CODE_200000') AND code <> 'CODE_100000';
/* [正規表現のポイント]
   1[0-9]{1,5}：1桁目が「1」で、2桁以降が「1文字以上5文字以下の数値」
    ⇒　つまり100000~199999の値 */

-- ヒットする範囲：CODE_200001 ~ CODE_300000
SELECT COUNT(*) FROM table WHERE (code REGEXP '^CODE_2[0-9]{5}$' OR code = 'CODE_300000') AND code <> 'CODE_200000'; 
/* [正規表現のポイント]
    2[0-9]{5}：1桁目が「2」で、2桁以降が「5文字の数値」
    ⇒　つまり200000~299999の値 */



/* 【50万ぐ切りで検索】 */
-- ヒットする範囲：CODE_1 ~ CODE_500000
SELECT COUNT(*) FROM table WHERE code REGEXP '^CODE_[0-9]{1,5}$' OR code REGEXP 'CODE_[1-4][0-9]{5}$' OR code = 'CODE_500000';
/* [正規表現のポイント]
   [1-4][0-9]{5}：1桁目が「1から4までの数値」で、2桁以降が「5文字の数値」
    ⇒　つまり100000~499999の値 */

-- ヒットする範囲：CODE_500001 ~ CODE_1000000
SELECT COUNT(*) FROM table WHERE (code REGEXP '^CODE_[5-9][0-9]{5}$' OR code = 'CODE_1000000') AND code <> 'CODE_500000';
/* [正規表現のポイント]
    [5-9][0-9]{5}：1桁目が「5から9までの数値」で、2桁以降が「5文字の数値」
    ⇒　つまり500000~999999の値 */

-- ヒットする範囲：CODE_1000001 ~ CODE_1500000
SELECT COUNT(*) FROM table WHERE (code REGEXP '^CODE_1[0-4][0-9]{5}$' OR code = 'CODE_1500000') AND code <> 'CODE_1000000';
/* [正規表現のポイント]
   1[0-4][0-9]{5}：1桁目が「1」で、2桁目が「0から4までの数値」、3桁以降が「5文字の数値」
    ⇒　つまり1000000~1499999の値 */

-- ヒットする範囲：CODE_1500001 ~ CODE_2000000
SELECT COUNT(*) FROM table WHERE (code REGEXP '^CODE_1[5-9][0-9]{5}$' OR code = 'CODE_1-3_2000000') AND code <> 'CODE_1500000';
/* [正規表現のポイント]
    1[5-9][0-9]{5}：1桁目が「1」で、2桁目が「5から9までの数値」、3桁以降が「5文字の数値」 
    ⇒　つまり1500000~1999999の値 */

 上記の正規表記のポイントに再三登場してきますが、｛n｝は直前の文字をn回繰り返すことを意味しており、[0-9]は0から9の数値を意味しています。

そうすると、[0-9]{5}は0から9までの数値を5回繰り返すことになるので、10000から99999の数値を表していることになります。

これが分かるといろいろと応用が利くので、文字列と数値が組み合わさった値の範囲検索が可能になるかと思います。

正規表現を使って電話番号を検索する

電話番号の種類はとても多いですが、よく使われるものに絞って、それらの規則性を把握していれば簡単に検索はできます。

カラム「phone」に電話番号が登録されていると仮定して、下記サンプルを見てください。

-- 050、070、080、090始まりの電話番号検索
WHERE phone REGEXP '0[5789]0-?[0-9]{4}-?[0-9]{4}';
/* [正規表現のポイント]
    '?' ：直前の文字が0回、または1回登場する。前の文字が省略可能な値の場合によく使う。今回はハイフン(-)。 */

-- 0120始まり(フリーダイアル)の電話番号検索
WHERE phone REGEXP '0120-?[0-9]{3}-?[0-9]{3}';

-- 0800始まりの電話番号検索
WHERE phone REGEXP '0800-?[0-9]{3}-?[0-9]{4}';

-- 0570(ナビダイアル)始まりの電話番号検索
WHERE phone REGEXP '0570-?[0-9]{6}';

正規表現を使ってメールアドレスを検索する

カラム「address」にメールアドレスが入っている例です。

-- メールアドレス
-- ASCIIコードの文字範囲で半角英数字を範囲指定するようにしてます。
WHERE address REGEXP '^[a-zA-Z0-9!-~]+@[a-zA-Z0-9-]+(\.)[a-zA-Z0-9-]+$';
/* [正規表現のポイント]
    [a-z] ：小文字の英字
    [A-Z] ：大文字の英字
    [0-9] ：0から9までの数値
    [!-~] ：ASCIIコードで決められている文字コードの範囲。「!」で始まり「~」で終わる。
    (文字) ：グループ化。カッコ内の文字を一括りの値として扱う
    '*' ：直前の文字が0回以上繰り返される
    '+' ：直前の文字が1回以上繰り返される
    '\．' ：エスケープ文字の「\」を「.（カンマ）」の前に組み合わせることで、文字列のカンマとして扱う*/


-- 上記例だとメールアドレスで使えない文字まで入ってしまうので、下記サイトを参考に正規表現の条件を修正
WHERE address REGEXP '^[a-zA-Z0-9.!#$%&\'*+\/=?^_`{|}~-]+@[a-zA-Z0-9-]+(?:\.[a-zA-Z0-9-]+)*$';
/* [正規表現のポイント]
　　'(?:)' ：キャプチャしないという意味。キャプチャとは、前の処理でカッコの中の正規表現でヒットした文字列を保持しておき、
　　　　　　　後で使いまわせる機能。グループ化だけが目的でキャプチャをしたくない場合はこの書き方を使う。
*/

参考HP：メールアドレスを表す現実的な正規表現

 まとめ

 今回、MySQLで正規表現を使った検索について解説しました。

 正規表現はMySQLなどのデータベースに限らず、PHPやJavaといったプログラムの中でもよく使われるのでマスタしておくことに越したことはないと思います。

 自分も正規表現について内容をまとめれたら、記事にしてみようと思います。