受講料が最大70%OFF 受講料が最大70%OFF

SQLで文字列操作をおこなう関数substringのサンプルコードを紹介しています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

SQLのsubstring関数の基本的な使い方


基本の構文は、「substring(カラム名または値,開始位置、終了位置)」です。

DBMSによって、文法が異なります。以下は、MySQLで動作するサンプルコードです。

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])」。

参考)Oracle公式ドキュメント SUBSTR

文字列の左端、右端を取得するleft関数、right関数がないためsubstr関数で代替します。

それぞれ以下のように記述します。

開始位置にマイナスを指定することで、文字列の右端から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の指定方法を流用できるようにいくつかのシノニムが用意されています。

これにより、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を使うとレスポンス悪化の可能性あり

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。