SQLで変数やパターンを使ってLIKEを活用する方法

記事

Toshihiko Minamoto · 11 hr 前 8m read

年月を重ねるにつれて、InterSystems開発者コミュニティで特定のSQLの質問が何度も繰り返されることに気づくようになりました。特にさまざまなコンテキストでのLIKE述語の使用についてです。よくあるバリエーションには、以下のようなものがあります。

また、さらに多くの派生パターンがあります。そこで、InterSystems IRIS SQLでのLIKEの仕組みに焦点を当てた記事を書くことにしました。特に、埋め込みSQL、動的SQL、クラスクエリで変数を使用する方法について取り上げ、パターンのエスケープ、特殊文字検索についても解説しています。

まず最初に、InterSystems IRIS SQLでは、SQL標準の新しいバージョンを実装しているその他のリレーショナルDBMSで利用できるほとんどの機能を提供していることを述べておきたいと思います。同時に、IRISではリレーショナルアクセスだけでなく、オブジェクトモデルやドキュメントモデルを使って同じデータにアクセスできることも重要なポイントです。

それでは、LIKE述語と、SQLでのパターンマッチングの使い方を詳しく見ていきましょう。

基本情報

まずは基本から見ていきましょう。LIKE述語は、列の値が指定したパターンに一致するかどうかに基づいてレコードを絞り込むために、SELECT、UPDATE、またはDELETEステートメントのWHERE（またはHAVING）句で使用されます。基本構文は次のとおりです。

SELECT column1, column2
  FROM table_name
 WHERE column_name LIKE pattern;

パターンには、リテラル文字と2つの主要なワイルドカード文字を含めることができます。

%（パーセント記号）：0文字、1文字、またはそれ以上の文字を表します。例えば、'A%' は 'A' で始まる文字列と一致し、'%Z' は 'Z' で終わる文字列と一致し、'%XY%' は 'XY' を含む文字列と一致します。
_（アンダースコア）：1文字を表します。例えば、'A_C' は 'ABC' 、'AEC' などと一致しますが、'AC' または 'ABBC' とは一致しません。

例えば、以下のクエリは Employees テーブルから「Jo」で始まるすべての名前を取得します。

SELECT Name
  FROM Employees
 WHERE Name LIKE 'Jo%';

また、このクエリは2番目の文字が「a」のすべての名前を取得します。

SELECT Name
  FROM Employees
 WHERE Name LIKE '_a%';

LIKEが等価比較ではなくパターンマッチングを実行することを理解することは重要です。 'ABC' LIKE 'ABC' はtrueと評価されますが、正確な文字列の一致には通常のように「=」演算子を使う方が効率的です（'ABC' = 'ABC'）。 LIKEは、あいまいなマッチングや部分文字列の検索が必要な場合に特に適しています。

検索フレーズに特殊文字を含める（エスケープ句）

ワイルドカード（例：%）をLIKEステートメントで使用する必要があり、通常の文字として扱いたい場合に、 <1>ESCAPE1>句はとても便利です。この場合、ESCAPE句を使用してエスケープ文字を定義することができます。パターン内のエスケープ文字の直後に続く文字は、ワイルドカードではなくリテラル文字として扱われます。例えば、以下のデータがあるとします。

INSERT INTO Post.Promos(name, description) VALUES('Test 1', 'This is 40% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 2', 'This is 50% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 3', 'This is 10% discount')
INSERT INTO Post.Promos(name, description) VALUES('Test 4', 'some description')

説明内に「50% discount」が含まれるすべてのプロモを見つけたい場合、以下のクエリを使用できます。

SELECT Name
  FROM Post.Promos
 WHERE Description LIKE '%50\% discount' ESCAPE '\'

この例では、\はエスケープ文字として定義されているため、システムは「%」記号をワイルドカードではなくリテラルとして扱い、説明に「50% discount」というリテラルのフレーズが含まれる割引を検索します。

特殊文字を見つける

特殊文字をいくつか検索する必要がある、または文字列内のすべての特殊文字を検索する必要がある場合、LIKE述語は使用できません。%MATCHESを使用する必要があります。値を、リテラル文字、ワイルドカード、範囲を含むパターン文字列と照合します。 %SelectModeの設定に関係なく、パターンは常に論理形式で指定する必要があることに注意してください。特殊文字が含まれるすべての値を検索したい場合、%MATCHESを使って次のように検索することができます。

SELECT * 
  FROM Post.Promos p
 WHERE p.description %MATCHES '*[!”%#$&”()*+,-./:;<=>?@\%\_]*'

これは、リストされたシンボルの少なくとも1つが含まれている説明を検索します。 ESCAPE句を使用してエスケープ文字を指定することもできますが、デフォルトは「\」に設定されているため、ここでの例のように省略することができます。

上記のクエリは、40%、50%、10%割引が含まれている3つの行のみを返します。

また、この述語はIRISのパターンマッチングの一般ルールに従います。例えば、プレースホルダーには、「_」ではなく「?」を使用します。

SELECT * 
  FROM Post.Promos p
 WHERE p.name %MATCHES '???? [0-9]'

これは、任意の4文字、スペース、そして数字1桁で構成されるすべての名前を検索します。

パターンの話題に触れたもついでに、%PATTERN述語も紹介します。これを使うと、文字タイプコードやリテラルを組み合わせたパターンをデータ値と照合させることができます。上記と同じ検索を実行するには、以下のクエリを書くことができます。

SELECT * 
  FROM Post.Promos p
 WHERE p.name %PATTERN '1U.L1" "1N'

これは以下に一致します。

1U — 1つの大文字
.L — 小文字
1" " — 1つのスペース
1N — 1桁の数字

LIKEと変数の使用

では、クエリでどのように変数を使用できるのかを見てみましょう。 Object ScriptコードでSQLステートメントを使うには、埋め込みSQL、動的SQL、クラスクエリの3つの方法があります。

埋め込みSQL

埋め込みSQLステートメントに値を渡すには、名前付きパラメーター（つまり、入力、出力ホスト変数、またはその両方）を使用する必要があります。つまり、名前を付ける必要があります。例えば、50% discount（割引）が含まれるすべてのプロモーションを見つけたい場合、以下のクエリを作成します。

 set param = "50\% discount"
 &sql(DECLARE C1 CURSOR FOR
       SELECT Name
         INTO :nameout
         FROM Post.Promos
        WHERE Description LIKE '%'_:param_'%' ESCAPE '\')
 &sql(OPEN C1)
       QUIT:(SQLCODE'=0)
 &sql(FETCH C1)
 WHILE (SQLCODE = 0) {
     WRITE nameout,!
    &sql(FETCH C1) }
  &sql(CLOSE C1)

入力ホスト変数はparamで、値は「50% discount」です。クエリに、%が任意の長さの文字列を表すワイルドカードではなく、パラメーターの一部であることを理解させるために、ESCAPE句を使用しています。

また、二重引用符と一重引用符を誤って配置しないでください。

SQLでは、予約語であるフィールド、例えば「Group」を示すために二重引用符を使用します。任意の文字列を扱うには、一重引用符を使用します。
ObjectScriptでは、任意の文字列を扱うために二重引用符を使用し、一重引用符は文字列とはまったく関係がなく、単項演算子NOTです。

パラメーターを使用する場合、二重引用符内に一重引用符を配置する必要はありません

~~set param = "~~ '~~50\% discount~~ ' "

コンパイラに文字列であること、またはそれに類するものであることを示す必要がないためです。この場合、エンジンは検索文字列の一部として一重引用符を探します。

上記の例の出力ホスト変数は「nameout」で、Name列の値が格納され、後でコード内で使用することができます。

動的SQL

バージョン2015.2移行、動的SQLは次の2つの方法でクエリにリテラル値入力を受け付けることができます。

「?」文字を使用して実行時間に指定される入力パラメーター
準備時間に指定した入力ホスト変数

2つ目のアプローチは、上記の埋め込みSQLと同じアイデアに従います。

 set param = "50\% discount"
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT Name"
 set myquery(2) = "FROM Post.Promos"
 set myquery(3) = "WHERE Description LIKE '%'_:param_'%' ESCAPE '\'"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute()
 while tResult.%Next() {
     write tResult.Name, !
 }

ただし、%SQL.StatementResult（tStatement.%Execute()の結果タイプ）には、SELECTステートメント内の列を参照しているすべてのプロパティがあるため、出力ホスト変数は必要はありません。

1つ目のアプローチでは、パラメーターの位置にクエスチョンマークを配置し、%Execute() を呼び出す際に、ステートメント内の「?」と同じ順序でパラメーターの値を渡す必要があります。

 set param = "50\% discount"
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT Name"
 set myquery(2) = "FROM Post.Promos"
 set myquery(3) = "WHERE Description LIKE '%'_?_'%' ESCAPE '\'"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute(param)
 while tResult.%Next() {
     write tResult.Name, !
 }

クラスクエリ

入力ホスト変数は、埋め込みSQLや動的SQLと同じ規則に従って、クラスクエリでも使用されます。

Query GetDiscount(param As %String) As %SQLQuery [ SqlProc ]
{
SELECT Name FROM Post.Promos
 WHERE (Description LIKE '%'_:param_'%' ESCAPE '\')
}

クエリを呼び出す際に、メソッドのシグネチャで記述されているものと同じ順序でパラメーターを渡します。

SELECT *
  FROM Post.Promos_GetDiscount('50\% discount')

この記事が、時々寄せられるよくある質問の少なくとも一部に答えるものとなれば幸いです

パフォーマンスの考慮事項やベストプラクティスについてより詳細に説明した記事にご興味がある場合、またはご意見・ご感想がありましたら、以下のコメント欄にお寄せください。

関連する InterSystems ドキュメントの記事を確認する

@Iryna Mykhailovaさんが書いた元の記事へ