SQL Serverのエスケープ処理についてまとめています。
escapeはSQL Serverのワイルドカード指定をエスケープする指定
Microsoft SQL ServerやPostgreSQLでは、likeによるワイルドカード文字のパターンマッチングで、ワイルドカード文字自身をエスケープすることができます。
例えば、以下の様なwhere条件を指定可能です。以下の条件は、「!」をエスケープ文字に指定するという意味になり、文字列”30%”を含む任意のデータにマッチします。
- WHERE comment LIKE '%30!%%' ESCAPE '!
なお、ESCAPE句がない場合には、文字列”30″にマッチします。
参考）LIKE (Transact-SQL) – SQL Server | Microsoft Docs
以下のSQLは、テーブルmytbl2のc1カラムに対して、「30-50%」という文字列を含むデータを抽出します。ワイルドカードとしての%と、マッチング文字列としての%が混在しています。
- USE tempdb;
- GO
- IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
- WHERE TABLE_NAME = 'mytbl2')
- DROP TABLE mytbl2;
- GO
- USE tempdb;
- GO
- CREATE TABLE mytbl2
- (
- c1 sysname
- );
- GO
- INSERT mytbl2 VALUES ('Discount is 30-50% off'), ('Discount is .30-.50 off');
- GO
- SELECT c1
- FROM mytbl2
- WHERE c1 LIKE '%30-50!% off%' ESCAPE '!';
- GO
PostgreSQLでは、escapeを指定しない場合はバックスラッシュがエスケープ文字として使われます。以下の２つのSQLは、同じ結果になります。
- SELECT c1
- FROM mytbl2
- WHERE c1 LIKE '%30-50!% off%' ESCAPE '!';
- SELECT c1
- FROM mytbl2
- WHERE c1 LIKE '%30-50\% off%';
MySQLでのSQLのエスケープ処理
MySQLではescapeは使えず、エスケープ文字はバックスラッシュ（円マーク）固定です。例外として、シングルクォートを二つ続けて書くと、「エスケープされたシングルクォート」となります。
なお、ダブルクォートでシングルクォート２つを囲むと、シングルクォート２つとなります。
- mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
- +-------+---------+-----------+--------+--------+
- | hello | "hello" | ""hello"" | hel'lo | 'hello |
- +-------+---------+-----------+--------+--------+
- mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
- +-------+---------+-----------+--------+--------+
- | hello | 'hello' | ''hello'' | hel"lo | "hello |
- +-------+---------+-----------+--------+--------+
- mysql> SELECT 'This\nIs\nFour\nLines';
- +--------------------+
- | This
- Is
- Four
- Lines |
- +--------------------+
- mysql> SELECT 'disappearing\ backslash';
- +------------------------+
- | disappearing backslash |
- +------------------------+
MySQL :: MySQL 5.6 リファレンスマニュアル :: 9.1.1 文字列リテラル
また、改行やタブなどの制御文字をあらわす際にもバックスラッシュが使われます。
- \0 NULL
- \n 改行文字
- \t タブ文字
- \\ 単なる文字としてのバックスラッシュ
なお、バックスラッシュでエスケープをおこなうのは標準SQLではなく、独自拡張となるため、データベースによって仕様が異なります。
【関連記事】
▶SQLとはどういうもの？ 独自拡張と標準SQLの大きな違いって、何？
なお、MySQLでは、NO_BACKSLASH_ESCAPESを設定すると、バックスラッシュが無効になります。設定後に接続したクライアントは、バックスラッシュがそのままの文字として扱われます。接続中のクライアントは、再接続後に設定が有効になります。
- // バックスラッシュが有効で、\nが改行として扱われる。
- mysql> select 'a\nb\nc\n' as col1;
- +--------+
- | col1 |
- +--------+
- | a
- b
- c
- |
- +--------+
- 1 row in set (0.00 sec)
- // NO_BACKSLASH_ESCAPESを設定
- mysql> set GLOBAL sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';
- Query OK, 0 rows affected (0.00 sec)
- // 設定内容を確認
- mysql> select @@GLOBAL.sql_mode;
- +--------------------------------------------------------------------------------------------------------------------------------------------+
- | @@GLOBAL.sql_mode |
- +--------------------------------------------------------------------------------------------------------------------------------------------+
- | ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
- +--------------------------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
- //一度、接続を終了する
- mysql> exit
- Bye
- //再接続して同じSQLを実行すると、バックスラッシュが文字として扱われている
- mysql> select 'a\nb\nc\n';
- +-----------+
- | a\nb\nc\n |
- +-----------+
- | a\nb\nc\n |
- +-----------+
- 1 row in set (0.00 sec)
Oracleのエスケープ処理
Oracleでは、バックスラッシュまたは、中括弧「{}」を使ってエスケープを行います。
また、SQL@Plus内では、set escape エスケープ文字と指定することで、エスケープシーケンス文字を変更することが可能です。
また、Oracleでは改行を表す\nは指定できません。CR（キャリッジリターン）はCHR(13)、LF（ラインフィード）はCHR(10)など、制御文字はCHR関数で表します。なお、OSによって改行はCR+LF（Windows系）の場合と、LFのみ（UNIX系）の場合があります。
まとめ
- SQL Server、PostgreSQLではescapeでエスケープ文字を変更可能
- MySQLでは、設定によりバックスラッシュによるエスケープをオン・オフ可能
- Oracleは、バックスラッシュを使った制御文字が使えないため、CHR関数を使って指定する
