データベースから取り出した文字列をrubyやphpなどのプログラムのsubstr関数を使って必要な部分を切り出し、Webで表示するテキストを作る処理は、ほとんどのシステムで使われています。しかし、その処理をデータベース側でもやれるのをご存知でしょうか。今回は、MySQLで文字列を切り出すsubstring関数について解説します。
MySQLの文字列を切り出す関数
プログラム言語の多くには、文字列を加工する関数が組み込まれています。そして、その中でもよく使われる機能の1つが、文字列の一部を切り出すsubstrなどの関数ではないでしょうか。そして、MySQLにも、抽出した文字列を加工して出力するための関数が組み込まれています。
なお、SQLに組み込んで使われるsubstr関数は、残念ながらプログラム言語のような細かい処理は苦手です。大量のデータを高速で処理することが可能な点がメリットと言えるでしょう。
MySQLの文字切り出し関数はsubstring
WebシステムでMySQLを利用する場合、データベースに格納した文字列を加工するなら、RubyやPHPなどのプログラム側で処理するのが一般的です。しかし、MySQL側でもある程度文字列を加工できます。場合によっては、データベース側で加工した方が効率的なケースもあります。
そのような文字列を加工する関数でよく使われるのが、元の文字列から特定の文字を切り出す処理です。そして、MySQLでは、文字を切り出す関数はsubstring()です。また、切り出す位置によっては、right()やleft()といった関数も利用できます。
SQLを移植する場合の注意点
SQLが使えるデータベース管理ソフトで文字列を切り出す関数が使えるのはMySQLだけではありません。Oracleをはじめ、他のSQLデータベース管理ソフトにも同様の機能が備わっています。しかし、他のSQLデータベース管理ソフトで用意されている関数は、substr()やmid()などMySQLとは違う関数です。そのため、SQLを移植する場合には、データベース管理ソフトに合わせて修正する必要があるので注意してください。
- MySQL：substring()
- Oracle：substr()
- PostgreSQL：substr()
- SQL server：substring()
- Access：mid()
mariadbならsubstrも使える
データベース管理ソフトの標準ともいえるOracleと関数名が違っていると、移植などでトラブルが発生します。そのため、MySQLとOracleの文字を切り出す関数が違っていることは、デメリットと言えるでしょう。しかし、最新版のMySQL 8.x系、および、MySQLと互換性のあるmariadb 10.3以降のバージョンでは、Oracleと同じsubstr()も使えます。
なお、mariadbは、最新のLinuxのディストリビューションで標準でインストールされており、また、最新版ではOracleとの互換性も高いのが特徴です。ぜひ、最新のLinuxにインストールされたmariadbのsubstr()を利用してください。
MySQLのsubstring関数の基本
MySQLのsubstinrg関数は、引数で指定した文字列の中の、指定した場所から、指定した文字数文の文字列を取得する関数です。ちょうどプログラミング言語のCやphpのsubstr()と同じ機能と考えてよいでしょう。しかし、SQLのSELECT文に組み込んで使われるため、抽出済みのリストの全てに適用されることになることから、プログラム言語のような細かい調整はできません。
substring関数の文法
substring関数は3つの引数を必要とし、第1引数は対象となる文字列、第2引数は切り出し開始位置、第3引数は切り出し文字数です。
substring( 文字列, 切り取り開始位置, 切り取る文字数 )
なお、切り取る文字数は省略可能で、省略した場合は切り取り開始位置から最後までの文字列を切り出します。
substring関数を使った例
select substring(post_title,1,40) from wp_posts where id=10;
この例では、wp_postsというテーブルのid=10という条件で抽出したpost_titleに格納された文字列を、1文字目から半角文字で40文字分を切り出して表示します。
mariadbならsubtrも使える
MySQLと互換性があり、最近のメジャーなLinuxディストリビューションで標準でインストールされているmariadbなら、substr関数も使えます。そして、このsubstr関数は、substringと同じ機能なので、文法も同じです。
substr( 文字列, 切り取り開始位置, 切り取る文字数 )
substr関数を使った例
select substr(post_title,1,40) from wp_posts where id=10;
substring関数の応用例
先ほども解説しましたが、MySQLのsubstring関数はSQLの中で使われることから、対象になる文字列に応じて切り出し位置を変えたり、切り出す文字数を変更したり、といった細かい処理はできません。しかし、簡単な記述でリストをまとめて加工できるのがSQLのメリットです。そして、SQLの書き方を工夫すれば、複雑な操作も可能です。
次から、そのようなMySQLのsubstring関数を使った応用例をご紹介します。
特定の文字以降の文字列を切り出す
今回紹介しているMySQLのsubstring関数を使えば、文字列の中にある特定の文字を目印に、その右側や左側の文字を切り出す、といった処理をデータベース側で処理させることが可能です。そのためには、特定の文字の位置を調べるMySQLの関数であるinstr()やlocate()を活用します。
例えば、Eメールアドレスの「@」の右側を切り出す処理は、次のように書けます。
Eメールアドレスから@の右側を切り出す処理
select email, substring(email, instr(email, '@')+1 ) from emaillist;
このSQLを実行すると、Eメールアドレス「postmaster@posted.one」から、@の右側の「posted.one」を切り出せます。
括弧内の文字列を切り出す
先ほどの文字の位置を調べるinstr()関数を組み合わせれば、ある文字列の中に書かれた括弧内の文字列だけを切り出すこともできます。
例えば、商品名の中に記述された括弧内の文字を切り出すSQLは次のとおりです。
select substring(商品名,
instr(商品名, '(')+1,
instr(商品名, ')') - instr(商品名, '(') -1
)
from 商品リスト;
このSQLを実行すると、商品名「BSMRW100BK Wireless Mouse (Black)」の括弧に囲まれた「Black」のみを切り出せます。
substring_indexを利用する
プログラムで文字を切り出す際、目印の文字の何番目から何文字、といった文字列を切り出すこともあります。プログラムを使ってこのような処理を実現するには、文字列を一旦配列に変換し、その配列から切り出したい文字列を選ぶ、という方法が使えます。この方法は、MySQLならSQLで記述できます。
MySQLのsubstring_indexは、ちょうどセパレータの文字を指定し、元の文字列を配列にして、対象の文字列を取り出すことが可能です。
substring_indexの文法
substring_index（ 文字列、区切り文字、区切られた文字列の番号）
substring_indexは、文字列を区切り文字を使って、複数の文字列に分割し、区切られた文字列の番号で指定された文字列を表示します。なお、区切られた文字列の番号に正の数を指定した場合は左から順に数え、負の数を指定した場合は右から順に数えます。
例えば、下記のような括弧を含む文字列から、最も右側の括弧内の文字を切り出すSQLは、次のように書けます。
文字列の最も右側の括弧内の文字を切り出すSQL
select left(
substring_index(name, '(', -1),
instr( substring_index(name, '(', -1), ')') -1
) from lista1;
元の文字列
'test - (White)' 'sample (M) free (Black)' 'study (L) normal(A) (Blue)' 'lecture (S) high(B) (Green) option'
SQLの実行例
White Black Blue Green
まとめ
Webシステムなど、プログラムからMySQLを利用する場合、今回紹介した文字列の切り出しはプログラム側でも処理できます。さらに、その方が細かい設定などもできるので、便利なケースが多いでしょう。
しかし、サーバーの構成や扱うデータの大きさなどによっては、MySQL側で処理した方が有利なケースもたくさんあります。Webシステムのパフォーマンスを評価して、最適化する手法としてMySQLのsubstr関数などを活用し、文字列の加工をデータベース側で実行することを検討されてはいかがでしょうか。
