先日、\n・%・\のような、検索にエスケープが必要な文字を抽出する機会がありました。調べてみると方法が複数あり、どれを使えばよいか迷ってしまいました。
そこで今回は、LIKE・CONTAINS_SUBSTR・REGEXP_CONTAINS を用いてこれらの文字を抽出する方法をまとめます。
エスケープとは何か？
エスケープとは、エスケープ文字を前に置くことによって、文字列中の文字を通常とは異なる方法で解釈させるための仕組みです。エスケープ文字としては\（バックスラッシュ）がよく用いられます。
例えば、BigQueryでは\nは改行文字を表し、\\nは「\n」という文字そのものを表します。ややこしいですが、前者の「特殊な文字の表現」と後者の「特殊な意味の無効化」はどちらもエスケープと呼ばれます。
プレフィックス「r」
プレフィックス（接頭辞）のrは、raw string（生の文字列）の略です。BigQuery では文字リテラルの前にrをつけると、その文字列内のバックスラッシュがエスケープ文字として解釈されなくなります。実際に例を見てみましょう。
本記事では、以下のテーブルを使って検証を行います。
| id | input_value | description |
| 1 | 改行がひとつ | |
| 2 | \n | 「\n」という文字列 |
| 3 | % | 「%」という文字列 |
| 4 | \ | 「\」という文字列 |
| 5 | hello world | hello + 改行 + world |
| 6 | hello\nworld | 「hello\nworld」という文字列 |
| 7 | hello%world | 「hello%world」という文字列 |
| 8 | hello\world | 「hello\world」という文字列 |
試しに、id2の「\n」という文字列を抽出してみましょう。
rなしの場合、SQLでは\nは改行文字として認識されてしまいます。そのため、バックスラッシュでエスケープをし、\\nと書く必要があります。
SELECT * FROM your_table WHERE input_value='\\n'rプレフィックスを使えば、より直感的に書けます。
SELECT * FROM your_table WHERE input_value=r'\n'上ふたつのクエリでは、どちらも同じ結果が得られます。
| id | input_value | description |
| 2 | \n | 「\n」という文字列 |
以降の例では基本的にrを使って記述します。
LIKEで抽出する方法
LIKEを使ったあいまい検索で各文字を抽出する方法です。
「\n」を抽出する（id2, 6）
LIKE演算子は文字リテラルを受け取った後、デフォルトで\（バックスラッシュ）がエスケープ文字として扱われます。そのため、rを使ったとしても一段階エスケープをする必要があります。
SELECT * FROM your_table WHERE input_value LIKE r'%\\n%'「%」を抽出する（id3, 7）
LIKEは受け取った文字リテラル中の%をワイルドカードとして解釈します。そのためエスケープが必要です。
SELECT * FROM your_table WHERE input_value LIKE r'%\%%'「\」を抽出する（id2, 4, 6, 8）
同様の理由で、バックスラッシュを検索したいときもLIKE用のエスケープが必要です。
SELECT * FROM your_table WHERE input_value LIKE r'%\\%'CONTAINS_SUBSTRで抽出する方法
WHERE CONTAINS_SUBSTR(列, 指定した文字)で、指定した文字を含む行を抽出できます。なお、第一引数には文字列やテーブルを渡すこともできますが、ここでは割愛します。
「\n」を抽出する（id2, 6）
SELECT * FROM your_table WHERE CONTAINS_SUBSTR(input_value, r'\n')「%」を抽出する（id3, 7）
CONTAINS_SUBSTRはLIKEと違い、%は特別な文字として扱われないため、rなしでもそのまま書けます。
SELECT * FROM your_table WHERE CONTAINS_SUBSTR(input_value, '%')「\」を抽出する（id2, 4, 6, 8）
r'\'と書くと、バックスラッシュが末尾のクォートをエスケープしてしまい構文エラーになります。そのため、ここではrを使わずに記述します。
SELECT * FROM your_table WHERE CONTAINS_SUBSTR(input_value, '\\')REGEXP_CONTAINSで抽出する方法
WHERE REGEXP_CONTAINS(列, 正規表現)で、正規表現にマッチする行を抽出できます。CONTAINS_SUBSTRと異なり、第二引数は正規表現として解釈されるため、エスケープが一段階多く必要になります。
「\n」を抽出する（id2, 6）
SELECT * FROM your_table WHERE REGEXP_CONTAINS(input_value, r'\\n')「%」を抽出する（id3, 7）
正規表現において%は特別な文字ではないため、そのまま書けます。
SELECT * FROM your_table WHERE REGEXP_CONTAINS(input_value, '%')「\」を抽出する（id2, 4, 6, 8）
SELECT * FROM your_table WHERE REGEXP_CONTAINS(input_value, r'\\')まとめ
用いる方法によって、エスケープが必要な回数など細やかな違いがあるのが分かったと思います。
ワイルドカードを使った検索をしたいときはLIKE、直感的に書きたいときはCONTAINS_SUBSTR、正規表現を使いたいときはREGEXP_CONTAINSを使うなど、用途によって使い分けるのが良いかもしれません。
