SQLのsubstr関数についてまとめています。Oracleのsubstr関数は、文字列の指定の長さを切り出す関数です。
SQL substr（Oracle）の構文
{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])
OracleのSUBSTRには、SUBSTRB、SUBSTRC、SUBSTR2、SUBSTR4といったバリエーションがあります。それぞれ文字列の位置指定に使う単位が異なります。
- SUBSTRB 文字数の代わりにバイト数を使用
- SUBSTRC 完全なUnicodeキャラクターを使用
- SUBSTR2 UCS2コードポイント使用
- SUBSTR4 UCS4コードポイントを使用
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])
もうひとつは、正規表現を指定して、一致するパターンを切り出す方法です。
substring(string from pattern for escape)
patternに正規表現を、escapeにエスケープ文字を指定します。
substring('Thomas' from '%#"o_a#"_' for '#')
substring
---------
oma
まとめ
- SUBSTRは、Oracleの文字列の部分切り出しの関数
- SUBSTRをwhere句に使用する場合、処理が遅くなるケースに注意
- 部分切り出し関数は、他のDBMSではsbstring関数として実装されている
