SQLで文字列操作をおこなう関数substringのサンプルコードを紹介しています。
以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。
SQLのsubstring関数の基本的な使い方
基本の構文は、「substring(カラム名または値,開始位置、終了位置)」です。
DBMSによって、文法が異なります。以下は、MｙSQLで動作するサンプルコードです。
SELECT concat( substring(first_name,1,1),'.',substring(last_name,1,1) ) as initial, first_name, last_name FROM `employees`
上記SQLは、employees（社員テーブル）から、first_name（姓名の名）の1文字目とlast_name（姓名の名）の1文字目をsubstring関数で抽出し、concat関数で連結してイニシャルを取得しています。
実行するとこうなります。
SQL実行結果
DBMSごとのsubstringの違い
substring関数はデータベースによって微妙に文法、引数などが異なります。
Oracleではsubstr
関数名がsubstrになります。
構文は、「substr(string, position [, length])」。
文字列の左端、右端を取得するleft関数、right関数がないためsubstr関数で代替します。
それぞれ以下のように記述します。
- left(x,3)の代替 substr(x,1,3)
- right(x,3) の代替 substr(x,-3)
開始位置にマイナスを指定することで、文字列の右端からx文字目までを抽出する動きになります。
また、Oracleではバイト数指定で文字列やバイナリを抽出できるsubstrbという専用関数が用意されています。
SQL Serverは、文字列操作関数が充実
SQL Serverでのsubstring構文は、「SUBSTRING ( expression ,start , length )」
数値、文字列、イメージ型を扱うことができます。
参考）SUBSTRING (Transact-SQL) – SQL Server | Microsoft Docs
他のDBMSと比べると、left関数、right関数に加えてLTRIM（左端の空白を除去）、RTRIM（右端の空白を除去）、STRING_SPLIT（指定文字で文字列を分割）が用意されているなど、文字列操作関数が充実しています。
PostgreSQLは、正規表現が使える
postgreSQLでのsubstring構文はカンマを使わない点に注意。
「substring(string from 開始位置 for 文字の長さ)」となります。
他のDBMSと比較すると正規表現が使える点が特徴です。POSIX正規表現と、SQL正規表現が仕様可能。
POSIX正規表現の場合は「substring(string from 正規表現のパターン)」、SQLの正規表現の場合は「substring(string from 正規表現パターン for エスケープ文字)」となります。
参考）PostgreSQL 11のマニュアル 9.4. 文字列関数と演算子
MySQL 他のDBMS表記をそのまま使える
MySQLのsubstringは、他のDBMSの指定方法を流用できるようにいくつかのシノニムが用意されています。
- SUBSTR(str,pos)
- SUBSTR(str FROM pos)
- SUBSTR(str,pos,len)
- SUBSTR(str FROM pos FOR len)
これにより、OracleやPostgreSQLの表記で、そのまま動作します。
参考）MySQL :: MySQL 8.0 Reference Manual :: 12.5 String Functions and Operators
また、MySQLにはsubstring_indexという関数が用意されていて、csv形式（カンマ区切り）やウェブのドメイン名指定（ドット区切り）、ファイルのパス指定（スラッシュや円マーク区切り）の文字列を指定番目の区切り文字で抽出することが容易になっています。
substringの注意点
日付をあらわす文字列から年月日を取得するには、substringよりdate_format
日付をあらわす文字列から、substringを使って年月日を抽出できます。
※hire_dateの形式は「YYYY-MM-DD」
select concat( substring(hire_date, 1, 4), '年', substring(hire_date, 6, 2), '月', substring(hire_date, 9, 2), '日' ) as yyyymmdd from employees
上記SQLは、employees（社員テーブル）のhire_date（雇用日）の1～4文字目を年、6～7文字目を月、9～10文字目を日としてconcat関数で連結して取得しています。
実行するとこうなります。
SQL実行結果
場合によっては、date_formatなどの日付フォーマット変換関数を使った方が簡単に記述できる場合もあります。
select date_format(hire_date, '%Y年%m月%d日') as yyyymmdd from employees
整数にもsubstringが使える
substring関数は、整数や小数にも適用可能です。※MySQLの場合
またsubstring関数で抽出した数値は、足し算などの演算が可能です。
SELECT substring(12345, 2, 2), substring(3.14159, 3, 2), substring(1234567, 3, 2)+ substring(7654321, 2, 3)
上記SQLは、「12345」の2~4文字目（23）、「3.14159」の3～4文字目（14）、「1234567」の3~4文字目（34）+「7654321」の2～4文字目（654）=688を表示します。
実行するとこうなります。
where条件にsubstringを使うと、レスポンスが悪くなる可能性あり
以下のSQLのようにwhere条件に関数を使うと、substringに限らずレスポンスが悪くなる可能性があります。設定されたインデックスが使用できず、テーブルフルスキャンが実行されるためです。
SELECT * FROM `salaries` where substring(emp_no,1,2) = 20 and substring(salary,1,2) = 60
※salaries（年収テーブル）のデータから、emp_no（社員番号）の先頭2桁が20、salary（年収）の上2桁が60のデータを取得します。
大量データの入ったテーブルにsubstringを使ったwhere条件を適用する場合は、別の絞り込み条件で対象処理件数を減らせないかどうか検討するようにしましょう。
まとめ
- substringの構文は、DBMSごとに微妙に異なる
- 日付文字列を扱うにはsubstringよりも日付フォ―マット関数のほうが簡単なケースあり
- 整数、小数に対してもsubstringが使えるケースあり
- where条件にsubstrを使うとレスポンス悪化の可能性あり
