misc.log

日常茶飯事とお仕事と

SQL ServerでのLike検索でエスケープすべき文字

ちょっと理由をうまく説明できなかったのでメモ。

SQLでデータベースから情報を取得する際に「Like」で指定する場合、ワイルドカードとして使える文字があります。そういう「ワイルドカード文字」のような特殊用途文字自体を検索する場合、「これは特殊文字だけど、今回は普通の文字とみなしてね」という指示をするのですが、これを「エスケープ」と呼びます。

で、MicrosoftSQL Serverの場合、エスケープしなければならない文字は下記の3つ。

  • %(パーセント文字) …… Like文で主に用いるワイルドカード文字。たとえば「Like '%文字'」と指定すると、何か0文字以上の適当な文字のあと、最後に「文字」で終わるデータが該当します。
  • _(アンダースコア、下線)…… Like文では「何か1文字」を表すワイルドカード文字。たとえば「Like '%です_'」と指定すると、何か適当な文字のあと、「です」ともう1文字がつながるデータが該当します。
  • [(左大括弧、Opening Bracket) …… Like文では複数の文字を列挙したり範囲指定して該当する文字を検索条件にする場合、その範囲を大括弧で囲んで示します。その左側のカッコです。たとえば「Like ’渡[辺部邊邉]'」と指定すると、「渡辺」「渡部」「渡邉」「渡邊」のいずれかが該当します。ほかにも「[0-9]」とすれば0~9の数字文字どれか、というような範囲指定も可能です。

これらは通常、範囲指定や複数に該当する条件を表すための特殊文字ですが、中には「データに 50% という文字が入っているものを探したい」といった要望もあるかとおもいます。そうするとパーセントの記号を検索する必要がありますね。このような場合、対象文字を大括弧で囲めばOKです。たとえば、「where data like '%50[%]%'」とすれば、「文中に50%が入っている文字列」をdata列から探せます。このような特殊な文字の扱いをC#で行う場合、下記ブログで紹介されているようなコードでリプレースを掛ければOKです。

LIKE で検索するときの文字のエスケープ (SQL Server) / Do Design Space
https://sakapon.wordpress.com/2010/06/21/sqlserverlike/

たとえばこんな感じです。

string sql = Regex.Replace(originalSql, @"[%_\[]", "[$0]");

で、疑問が1つ……。

「なんで右大括弧はエスケープしなくてもいいのか?」

これなんですが、おそらく、そもそも上記3文字を「大括弧」でエスケープするのではなく、T-SQLでのLike検索で使える「複数の文字に合致する」という条件指定の大括弧、これの中では「%」「_」「[」はエスケープしなくてもそのまま使える、という仕様のようです。なので、大括弧で囲むことで、%の場合「%という1文字に一致する」という条件を指定したことになるのではないかと。逆に、大括弧の中では「^(キャレット、否定で利用)」、「-(ハイフン、範囲指定で利用)」、「](右大括弧、範囲指定の終わり)」はエスケープしなければそのままの文字としては使えません。

このあたりは下記のサイトで書かれています。

LIKE (Transact-SQL) / T-SQL Language Reference
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-2017

If the character after an escape character is not a wildcard character, the escape character is discarded and the character following the escape is treated as a regular character in the pattern. This includes the percent sign (%), underscore (_), and left bracket ([) wildcard characters when they are enclosed in double brackets ([ ]).

  • ワイルドカード文字以外がエスケープ文字の後ろに来た場合、エスケープ文字はないものとみなされてエスケープ文字の次の文字は普通にその「文字」として扱われる。
  • これは、大括弧で囲まれた %、_、[ に関しても同様(大括弧の中ではこれらはワイルドカード文字以外とみなされる)。

また、逆に、普通はワイルドカード扱いにならない「^」「-」「]」については、大括弧の中ではエスケープ対象のワイルドカード文字になる、ということのようです。

Also, within the double bracket characters ([ ]), escape characters can be used and the caret (^), hyphen (-), and right bracket (]) can be escaped.


セキュアプログラミング―失敗から学ぶ設計・実装・運用・管理

セキュアプログラミング―失敗から学ぶ設計・実装・運用・管理

WRITING SECURE CODE 第2版 上 (マイクロソフト公式解説書)

WRITING SECURE CODE 第2版 上 (マイクロソフト公式解説書)