PostgreSQLで正規表現の逆引き検索

TL;DR

正引き検索

eg: 4桁数字から始まる電話番号を検索する

SELECT * FROM users WHERE (tel ~ '^\d{4}-');

逆引き検索

eg: 会員の電話番号から合致するパターンを検索する

SELECT * FROM filters WHERE ('0120-123-456' ~ regexp);

Railsでの使い方

Filter.where("? ~ regexp", user.tel)

はじめに

最近、とある理由で会員の情報から、合致する全てのパターンを検索するというニッチな要望を対応していました。
例えば、会員にメルマガを送信する時、固定電話で登録していた方と、携帯で登録していた方と、法人様で登録していた方にはそれぞれ違う内容で送信したいとかの要望について、今まではハードコーディングでロジックに組み込んでいましたが、管理画面でフィルターを作れません。

そのために、パターンそのものをDBに登録して、一括配信する際に、どのフィルターに当てはまるかを判断する機能を作りました。

作成例

Model構成

user.rb

class User < ApplicationRecord
  # name: string
  # tel: sting
  # ...
end

filter.rb

class Filter < ApplicationRecord
  # name: string
  # regexp: string
  # text: text
  # ...
end

サンプルデータ

User.new(name: 'User1', tel: '090-1234-5678').save
User.new(name: 'User2', tel: '070-1234-5678').save
User.new(name: 'User3', tel: '03-1234-5678').save
User.new(name: 'User4', tel: '08012345678').save
User.new(name: 'User5', tel: '1234-5678').save

Filter.new(name: '携帯電話', regexp: '^0[1-9]0-?', text: '携帯会員です').save
Filter.new(name: '市外局番記入なし', regexp: '^\d{2,4}-?\d{3,4}$', text: '市外局番からご記入してください').save

実行例

user = User.find_by(name: 'User1')
filters = Filter.where("? ~ regexp", user.tel)

  Filter Load (14.8ms)  SELECT "filters".* FROM "filters" WHERE ('090-1234-5678' ~ regexp)
=> [#<Filter:0x00007fd1ee27ddb0
  id: 1,
  regexp: "^0[1-9]0-?",
  text: "携帯会員です"
  created_at: Tue, 31 May 2022 16:22:57 JST +09:00,
  updated_at: Tue, 31 May 2022 16:24:47 JST +09:00>]

参考

• PostgreSQLで正規表現
• MySQL 8.0 リファレンスマニュアル :: 12.8.2 正規表現
  • 余談ですが、MySQLで正規表現を使った検索では、\dのような特殊文字は使えません、[[:digit:]]のようなcharacter_classを使うべきです。