SQLでシングルクォートを含む文字列を追加、挿入するには

はじめに

シングルクォートとは、SQLでテキストを括るための区切りとして使われる文字のことだ。
アポストロフィーと呼ぶこともある。

キーボードでは数字の「7」のキーのところにある、「’」形をした文字だ。

英文では所有の意味があり頻繁に使われる。
そのため、SQLの区切り文字としての用途と、文字そのものとしての用途が競合することがよくある。

INSERT文やUPDATE文などの引数にシングルクォートを含む文字列を渡すと、途中で文字列が終了したものと誤認され、SQLエラーになってしまうことがあるのだ。

今回は、このようなケースを回避する方法を紹介する。

文字列中のシングルクォートは重ねる

例として「John’s father」という文字列をSQL文中で使う場合を考えてみよう。

結論から言うと、文字列中のシングルクォートを2つ重ねて「”」のように変換してからSQLを実行すると成功する。

UPDATE文の場合なら、例えば以下のようにすればよい。

UPDATE テーブル名 SET カラム名 = 'John''s father';

テキストの前後のシングルクォートはそのままにして、文字列中のシングルクォートのみ「’」を「”」に変換するのだ。

INSERT文や他の文の場合も同様だ。
文字列中のシングルクォートのみ、シングルクォート2つに変換しよう。

なお、「”」はダブルクォーテーション「”」では代用できない。
見た目はそっくりでもこれらは別の文字なので注意しよう。

あくまでも、シングルクォート2つにすることが肝要だ。

応用例:EXCEL上でSQLを自動生成している場合

EXCELやGoogleスプレッドシートなどを使ってSQLを自動生成している人は多いだろう。
例えば以下のようなやり方だ。

この例では「名前」列に人名などを入力すると、「SQL」列に設定してある式が働いてUPDATE文を生成する仕組みになっている。

赤枠で囲ってある箇所に書かれているのが式の内容だ。

名前が「John」や「Lucy」だとSQL文が正しく生成されるのだが、前出の「John’s father」の場合は、シングルクォートが悪さをして不正なSQL文が生成されてしまう。

これを回避するには「SUBSTITUTE関数」を使ってシングルクォート1つをシングルクォート2つに変換する仕組みを追加すると良い。

すると、以下のように、名前にシングルクォートが含まれる場合でもSQL文が正しく生成されるようになる。

上記の式をテキストで表記すると以下のようになる。

="UPDATE name_table SET name = '" & SUBSTITUTE(B4, "'", "''") & "';"

まとめ

SQLでシングルクォートを含む文字列を追加、挿入する方法を紹介した。

シングルクォートはSQL文中では「”」のように2つ重ねて表記する必要がある。
これにより、区切り文字としてのシングルクォートではなく、文字としてのシングルクォートと認識されるようになる。

応用例として、EXCELなどを使用してSQLを自動生成しているケースを想定し、上記の変換を自動的に行う式を紹介した。

コメントする

メールアドレスが公開されることはありません。