SQLが使えるDBサーバーでリストを作る際、文字列同志を結合できます。しかし、DBサーバーによって使い方が違います。そこで今回は、SQLにおける文字列の結合機能について、DBサーバー毎の差を中心に解説します。
目次
データベースで文字列を連結するメリット
多くのWebシステムは、データベースに格納された情報をSQLで取り出し、プログラムで加工してWebブラウザに表示させる、という方法が使われています。
その場合、SQLを実行するRubyなどのプログラム側で文字列を加工することも可能ですが、SQLで加工したデータを取り出すことも可能です。場合によっては、データベース側で加工した方が短い時間で処理でき、レスポンス速度が改善するケースもあります。
まずは、データベース側で文字列を結合するメリットについて解説します。
SQLでも文字列の加工はできる
Webエンジニアの中には、SQLを学び、Rubyなどのプログラムを使ったシステム開発で利用されている方も多いことでしょう。しかし、テーブルの連結や抽出条件の書き方などを詳しく学んだ方でも、データを取り出す際に文字列連結などの関数の使い方をマスターした方は少ないかもしれません。
以前なら、企業向けの情報システムのデータベースに格納された情報は、SQLで取り出してそのまま印刷したり、Excelに取り込んで業務に利用することが珍しくありませんでした。今でも、そういったSQLの作成を依頼されるデータベースエンジニアもいるようです。
そして、このようなSQLでは、Excelで人が読みやすいように、文字列を加工するための関数が使われています。その中でもよく使われる関数が、文字列同士を結合する関数です。
CSVへの出力はSQLだけでも可能
先ほど説明したように、データベースから必要な情報のみをCSVファイルとして取り出すSQLは、今でも使われています。その際によく使われるのが、パソコン用のクライアントソフトとバッチファイルの組み合わせです。
なお、OracleやSQL Serverといった企業向けのシステムで利用されるデータベース管理ソフトだけではなく、MySQLやPostgreSQLなどでも、SQLの結果をCSVファイルを書き出すことが可能です。
そして、そういったSQLでは、SQLで抽出した情報を、コンマで連結して1つの文字列として取り出す方法が使われています。そのため、もし、SQLを移植してプログラムで利用する場合は、SQLにおける文字列の連結を理解していなければなりません。
SQLの文字列連結方法は一つではない
このように、SQLでよく使われる文字列の連結機能ですが、SELECT文のようにSQLの標準として決まっている機能ではありません。そのため、データベース管理ソフトによって、使える関数が違うこともあります。
そのため、他のシステムで動いていたプログラムをコピーしたら、データベース管理ソフトが違うせいでSQLが動作しないこともあります。文字列を結合するといった、SQLで情報を加工して出力する処理ではこのようなことがあるので、データベース管理ソフトの違いについても理解しておきましょう。
Oracle Databaseの文字列連結方法
Linuxが誕生する前からデータベース管理ソフトとして使われているOracle Databaseは、SQLの出力をCSVファイルとしてファイルに書き出す手段としても使われます。そのため、CSV形式で出力する際、SELECT文に文字列連結機能を組み合わせて、加工済のデータにすることも可能です。
まずは、Oracle Databaseの文字列連結方法について解説します。
||演算子で接続する
Oracle Databaseの文字を列連する方法としてよく使われるのが、||演算子です。使い方は、出力したい文字を||で接続するだけと簡単なので、OracleでCSVファイルを出力する場合に使われます。
SELECT 値1 || ',' || 値2 || ',' || 値3 from テーブル1;
上記のSELECT文では、「値1,値2,値3」のようなCSV形式の文字列が出力されます。
CONCAT関数で接続する
Oracle Databaseで||演算子ではなく組み込みコマンドを使って文字列を結合する場合に使われるのがCONCAT関数です。使い方は、結合したい文字列を()の中の引数として、コンマで区切って指定します。
SELECT CONCAT(値1, ',', 値2, ',', 値3) FROM テーブル;
この例では、先ほどの||演算子と同じ結果を出力する例で、「値1,値2,値3」が出力されます。ただし、||演算子と違い、コンマで区切ることから、CSVを作る場合は使いにくいかもしれません。
区切り文字を間に挟むCONCAT_WS関数
一つ前で解説したCONCATはCSV形式を出力するのが面倒ですが、CONCAT_WS関数を使えば、もっと見やすく書くことが可能です。なお、CONCAT_WS関数は、区切り文字を間に挟んで文字列を結合する関数で、使い方はCONCAT関数と基本的に同じですが、最初の引数が区切り文字として使われます。
SELECT CONAT(',', 値1, 値2, 値3) FROM テーブル;
ただし、結合する文字列がNULLだった場合、それを無視して次の文字列を接続するので、CSVファイルとして使用する場合は、読み込んだデータがずれるかもしれません。NULLを含まないように条件を設定するなど、うまく抽出するSQLを作ってください。
MySQLの文字列連結方法
Linuxに標準でインストールされているMySQLは、Webエンジニアにお馴染みのデータベースではないでしょうか。なお、MySQLは、Oracle Databaseよりも後から開発されたデータベース管理ソフトなので、Oracle Databaseで使える多くの機能を利用できます。
ただし、PHPなどのプログラムからMySQLを利用する場合、プログラム側でデータを加工できるので、必ずしもMySQL側で文字列を結合する必要はありません。システム全体のパフォーマンスを考慮したうえで、この機能を利用するか検討してください。
次から、MySQLの文字列連結方法について、Oracle Databaseと比較して解説します。
MySQLの基本はCONCAT関数
デフォルト状態のMySQLで文字列を結合したい場合は、CONCAT関数を利用します。使い方はOracle Databaseと同じで、CONCATに続く()の中に、結合したい文字列をコンマで区切って指定します。
また、Oracle DatabaseのCONCAT_WSも使えます。なお、MySQLでもNULLがあるとスキップされるので、CSV形式で出力する際は注意が必要です。
MySQLで||演算子を使う方法
Oracle Databaseで使えた||演算子ですが、デフォルトのMySQLでは使えません。しかし、設定すれば||演算子で文字列を結合できるようになります。
一時的に||演算子を使えるようにするには、sql_mode変数にPIPES_AS_CONCATを登録するSQLとして実行します。また、恒久的に使うには、my.cnfファイルに定義しておきます。
SQLでPIPES_AS_CONCATを設定する場合の例。
SET sql_mode='PIPES_AS_CONCAT';
my.cnfに追加する設定例。
[mysqld]
sql_mode = ‘PIPES_AS_CONCAT’
SQL Serverの文字列連結方法
マイクロソフト製のSQL Serverは、長く同社のOSであるWindows Server専用のデータベース管理ソフトとして進化してきました。そのため、Oracle DatabaseやオープンソースのMySQLなどに無い独自の機能が特徴です。
次から、そのようなSQL Serverの文字列連結機能について解説します。
SQL Serverの文字列連結は+を使う
Oracle Databaseでは、文字列の連結に||演算子を使っていましたが、これはプログラム言語ではorに相当する演算子で、プログラムを学んだ方にとっては紛らわしい書き方です。それに対して、SQL Serverでは、文字列を連結する演算子として「+」を使います。プログラム言語Rubyでも文字列の連結に「+」を使うので、より解りやすい演算子と言えるでしょう。
SELECT 値1 + ',' + 値2 + ',' + 値3 from テーブル1;
この例では、「値1,値2,値3」のCSV形式の文字列を出力します。
SEL ServerでもCONCAT関数が使える
さらに、SQL Serverでは、CONCAT関数も使えます。使い方は、Oracle DatabaseやMySQLと同じで()の中に、コンマ区切りで連結したい文字列を記述します。また、第1引数に区切り文字を指定するCONCAT_WSも使えます。
文字列の結合はプログラムでも処理できる
WebシステムでSQLデータベースを使うなら、プログラム側で文字列を結合することも可能です。プログラムに慣れている方なら、わざざわSQLに記述するよりも簡単に書けるのではないでしょうか。しかし、Oracle Databaseなどで使わている既存のSQLを利用する場合、必要に応じてSQLを編集できるようにデータベース管理ソフト毎の文字列の結合を学んでおきましょう。
SQLの文字列の連結機能は、データベース管理ソフトによって使える関数が違うので、SQLを移植したら動作しないこともあります。