SQLiteに正規表現パターンを入れて入力にマッチするレコードを取り出す

背景

先頭にグループの意味を含んでいるIDがあり、そのIDを使ってそのIDが属するグループの情報を取り出すという実装がしたくなった。辞書的に使いたい。

全部のレコードを引っ張り出して、アプリケーション側(PHP)で正規表現を使って探してもいいのだが、DBならもっとスマートな実装方法があるのではなかろうかと。

SQL文で実現できたので、そのやり方を記事にする。

DBにすべてのIDを登録して、グループ名をつけておけば実現することができますが、今回の要件ではこれを使えません。

今回はレコードにパターンが記載されている例ですが、逆にSQL文でパターンを指定するケースのうち、パターンが簡単な場合は LIKE句や GLOB句が使用できる場合があります。こちらのほうが環境やバージョンに依存しずらいのでおすすめ。

SQLiteにpreg_match()をUDF(ユーザー定義関数)として登録する。

下記のコードをDBを開いた後あたりに記述する。DBを開いた時のインスタンスは各自違うと思うので、適宜変更してください。

SQLite3 クラスを使っている場合

if ($db->createFunction("preg_match", "preg_match", 2) === FALSE) {
    exit("Failed creating function\n");
}

PDO クラスを使っている場合

if ($db->sqliteCreateFunction('preg_match', 'preg_match', 2) === FALSE) {
    exit("Failed creating function\n");
}

SQL文でpreg_match()が使えるようになったので、SQL文に組み込む。

今回はIDを入力して、それが属するグループのレコードを取り出したいので、SELECT文とする。FROM句は通常通りテーブル名の指定。ポイントはWHERE句に指定するpreg_match()の呼び出し。

下記の例では、table1テーブルの内、id_patternカラム(TEXT型)に正規表現パターンが入っていて、ID"hogehoge"がマッチするレコードの全カラムを得ることができるSQL文の例。

SELECT * FROM table1 WHERE preg_match(id_pattern, "hogehoge");

このSQL文に限らず、特に外部からの入力を使う場合、安全のためにプリペアドステートメントを使用してください。