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

SQLのsubstr関数についてまとめています。Oracleのsubstr関数は、文字列の指定の長さを切り出す関数です。

SQL substr(Oracle)の構文

{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])

関連)Orcle公式ドキュメント:SUBSTR

OracleのSUBSTRには、SUBSTRB、SUBSTRC、SUBSTR2、SUBSTR4といったバリエーションがあります。それぞれ文字列の位置指定に使う単位が異なります。

OracleのSUBSTRの使用例です。

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

SUBSTR(‘ABCDEFG’,3,4)と指定すると、ABCDEFGという文字列の3文字目から始まる4文字を切り出します。

位置に負の値を指定すると、文字列の右から数えて文字列を切り出します。SUBSTR(‘ABCDEFG’,-5,4)の場合、ABCDEFGの開始位置-5は、文字列の最後から数えて5番目のCになり、Cから数えて4文字を切り出します。

where句の条件でSUBSTRを使うと遅くなるケース

SQLのwhere区でSUBSTRを使うと、処理が遅くなるケースがあります。以下は、test_ymdという日付データの格納されたカラムから年月部分のみを切り出し、2015年02月以降のデータを抽出しています。

select *
from Table01 
where SUBSTR(test_ymd,1,6)> '201502'

参考)SQL – Oracle 検索列にSUBSTRを使用せずに検索する方法|teratail

上記の記述はSQLとしては正しいのですが、カラムtest_ymdにSUBSTRを適用して条件とすると、test_ymdにインデックスが設定されていても、インデックスは使用されずテーブルフルスキャンとなってしまいます。データ件数が多い場合や、条件が複雑な場合は目に見えてパフォーマンスが落ちるんですね。

処理速度の低下が無視できない場合は、カラムtest_ymdではなく、比較する文字列201502をtest_ymdのデータ型に合わせるのが良いでしょう。test_ymdは、YYYYMMDD形式の8文字の文字列の前提です。

select *
from Table01 
where test_ymd > TO_CHAR(LAST_DAY(TO_DATE('201502', 'YYYYMM')), 'YYYYMMDD')

上記の指定により、test_ymdカラムにインデックスが指定してある場合は高速検索が可能になりますし、条件にも年月のみの指定ができます。

具体的には、201502をTO_DATEで日付型に変換し、LAST_DAYで月の最終日を算出し、さらにTO_CHARでYYYYMMDD形式の文字列に変換しています。

select TO_CHAR(LAST_DAY(TO_DATE('201502', 'YYYYMM')), 'YYYYMMDD') from DUAL;

TO_CHAR(LAST_DAY(TO_DATE('201502','YYYYMM')),'YYYYMMDD')
---------
20150228

Oracle以外のDBMSでは、文字列の切り出し関数はsubstring関数として実装されています。

【関連記事】
SQL substring関数の使い方 整数・小数も切り出し&加算可能

SQL Serverのsubsting関数

SQL Serverでは、文字列の部分切り出しはSUBSTRING関数として実装されています。

SUBSTRING ( expression ,start , length )  

関連)SUBSTRING (Transact-SQL) – SQL Server | Microsoft Docs

以下はSUBSTRINGを使ったSQLの例です。name(名前)の1文字目をInitial、3文字目~4文字目の2文字をThirdAndForthCharactersとして切り出しています。

SELECT name, SUBSTRING(name, 1, 1) AS Initial ,
SUBSTRING(name, 3, 2) AS ThirdAndFourthCharacters
FROM sys.databases  
WHERE database_id < 5;   

name	Initial	ThirdAndFourthCharacters
master	m	st
tempdb	t	mp
model	m	de
msdb	m	db

MySQLのsubsting関数

MySQLでは、SUBSTR()はSUBSTRING()のシノニム(別名)として定義されているので、SUBSTR()がそのまま利用可能です。

SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);

len(長さ)が1より小さい場合は、結果が空になります。

PosgreSQLのsubsting関数

PostgreSQLでは、substring関数に2種類の使い方ができます。ひとつは、数値による位置指定をする方法。

substring(string [from int] [for int])

関連)PostgreSQL公式:文字列関数と演算子

もうひとつは、正規表現を指定して、一致するパターンを切り出す方法です。

substring(string from pattern for escape)

patternに正規表現を、escapeにエスケープ文字を指定します。

substring('Thomas' from '%#"o_a#"_' for '#')

substring
---------
oma

まとめ

ポテパンダの一言メモ
  • SUBSTRは、Oracleの文字列の部分切り出しの関数
  • SUBSTRをwhere句に使用する場合、処理が遅くなるケースに注意
  • 部分切り出し関数は、他のDBMSではsbstring関数として実装されている

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

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

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

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

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

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

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

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

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

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

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