openGaussのパターンマッチング手法

openGaussデータベースは、文字列のパターン一致を実現するための複数の方法を提供しています。主に以下の3つのアプローチがあります：

• LIKE / ILIKE 演算子：基本的なワイルドカードによる部分一致検索
• SIMILAR TO：SQL標準に基づく正規表現風のパターンマッチング
• POSIX 正規表現：高度なテキスト処理が可能な強力な正規表現システム

加えて、一致した部分文字列の抽出・置換や、分割を行う関数群も用意されており、柔軟な文字列操作が可能です。

1. LIKEおよびILIKE演算子

最もシンプルでよく使われるパターンマッチング方式です。指定されたパターンに文字列が一致するかどうかを評価し、条件式として利用できます。

主なワイルドカード

大文字小文字の扱い

• LIKE：大文字小文字を区別する
• ILIKE：大文字小文字を区別しない

使用例

-- 完全一致（=と同じ）
SELECT 'hello' LIKE 'hello'; -- true

-- 先頭が'a'で始まるか
SELECT 'apple' LIKE 'a%'; -- true

-- 中間に'b'を含むか
SELECT 'abc' LIKE '%b%'; -- true

-- 2文字目が'b'である3文字の文字列か
SELECT 'abc' LIKE '_b_'; -- true

-- 大文字小文字の区別
SELECT 'test' LIKE 'TEST'; -- false
SELECT 'test' ILIKE 'TEST'; -- true

エスケープ文字のカスタマイズ（ESCAPE句）

検索対象に%や_そのものを含める場合は、エスケープ文字を使って明示します。

-- '$'をエスケープ文字として設定
WITH sample_data AS (
  SELECT 'price: 50%' AS text_value
  UNION ALL
  SELECT 'discount: 10%'
)
SELECT *
FROM sample_data
WHERE text_value LIKE 'price: $%%' ESCAPE '$';
-- 結果: 'price: 50%'

2. SIMILAR TO演算子

LIKEよりも洗練されたパターン記述が可能で、一部の正規表現構文を取り入れています。SQL標準仕様に準拠しています。

追加サポートされるパターン記法

使用例

-- 'ab'または'ac'を含む
SELECT 'abc' SIMILAR TO '%(ab|ac)%'; -- true

-- 'o'が0回以上続くパターン
SELECT 'xyz' SIMILAR TO '%o*%'; -- true（0回でもOK）

-- 'o'が1回以上必要
SELECT 'xyz' SIMILAR TO '%o+%'; -- false

-- 文字クラスの使用
SELECT 'cat' SIMILAR TO 'c[au]t'; -- true（'cut' or 'cat'）

3. POSIX正規表現

もっとも強力なパターンマッチング機能。Perl風の記法に近い形式で、複雑なテキスト解析が可能です。

主要なマッチング演算子

代表的なメタ文字

実践的な使用例

-- 大文字小文字を無視して一致
SELECT 'Hello' ~* 'hello'; -- true

-- 単語境界でのマッチ（'not'単体のみ）
SELECT 'notepad' ~ '\mnot\M'; -- false（'not'は単語として存在しない）
SELECT 'do not go' ~ '\mnot\M'; -- true

-- 複数の空白文字を許容（改行含む）
SELECT 'is   null' ~ '(is)\s+(null)'; -- true
SELECT 'is
null' ~ '(is)\s+(null)'; -- true（\sは改行も含む）

-- 条件付きパターン（'not'はあってもなくてもよい）
SELECT 'is null' ~ '(is)\s+(not\s+)?(null)'; -- true
SELECT 'is not null' ~ '(is)\s+(not\s+)?(null)'; -- true

正規表現関数の紹介

openGaussは以下の正規表現関数をサポートしています：

• regexp_substr(text, pattern)：一致部分を抽出
• regexp_replace(text, pattern, replacement)：一致部分を置換
• regexp_matches(text, pattern, 'g')：すべての一致を配列で取得
• regexp_split_to_table(text, pattern)：パターンで分割して行出力
• regexp_count(text, pattern)：一致回数をカウント
• regexp_like(text, pattern, 'i')：bool型で一致判定（i: 大文字小文字無視）

量指定子の違い（* vs ?）

置換処理において挙動が異なります。

-- (o)*：0回以上の'o'にマッチ → 最初の'b'までを対象
SELECT regexp_replace('foobarobaz', '(o)*b', 'X');
-- 結果: 'foXarobaz'

-- (o)?：0回または1回の'o' → 最初にマッチした'b'に適用
SELECT regexp_replace('foobarobaz', '(o)?b', 'Y');
-- 結果: 'foYarobaz'

まとめと使い分けのポイント

• LIKE/ILIKE：シンプルな前方一致・部分一致など、基本的な検索に最適
• SIMILAR TO：中程度の複雑さのパターン（例：複数選択、繰り返し）が必要な場合
• POSIX正規表現：高度なテキスト処理、正確な単語境界マッチ、関数連携が必要な場面で威力を発揮

正規表現は習得コストはありますが、一度使いこなせればログ解析、データクリーニング、構文チェックなど幅広い用途で活用できます。