SQL道場 文字列関数 SUBSTRING関数
SQLで文字列の部分切り出しするSUBSTRING関数について記載しています。
対応データベース：SQL Server MySQL PostgreSQL ANSI
目次
SUBSTRING関数の文法
SELECT SUBSTRING(対象文字列,開始位置,文字数) FROM テーブル
SELECT SUBSTRING(対象文字列 FROM 開始位置 FOR 文字数) FROM テーブル
【MySQL書式】
SELECT SUBSTRING(対象文字列,開始位置)
SELECT SUBSTRING(対象文字列 FROM 開始位置)
SELECT SUBSTRING(対象文字列,開始位置,文字数)
SELECT SUBSTRING(対象文字列 FROM 開始位置 FOR 文字数)| 引数 | 対象文字列 | 文字列型 |
| 開始位置 | 整数型 | |
| 文字数 | 整数型 | |
| 返値 | 文字列型 編集後の文字列が戻ります。 |
SQL Server
| 引数 | 対象文字列 | character、binary、text、ntext、または image式です。 |
| 開始位置 | 返された文字の開始位置を示す integer 式または bigint 式です。 (番号は 1 から開始し、これは式の最初の文字が 1 であることを意味します)。 「開始位置」に 1 より小さい値を指定した場合は、「対象文字列」に指定された文字の先頭から値が返されます。 この場合、返される文字数は、「開始位置」 + 「文字数」-1 の合計値か、0 のどちらか大きい方になります。 「開始位置」 が値式の文字数を上回る場合は、長さがゼロの式が返されます。 | |
| 文字数 | 「対象文字列」で返す文字数を正の整数または bigint 式で指定します。 「文字数」が負の場合はエラーが生成され、ステートメントは終了します。 「開始位置」 と「文字数」の合計が「対象文字列」の文字数を上回る場合は、「開始位置」 の先頭から値式全体が返されます。 | |
| 返値 | 「対象文字列」が、サポートされている文字データ型の 1 つである場合は、文字データが返されます。 「対象文字列」が、サポートされている binary データ型の 1 つである場合は、binary データが返されます。 返される文字列のデータ型は、指定した式のデータ型と同じです。ただし、下記の表の場合は例外です。 |
例外
| 指定した式 | 戻り値の型 |
|---|---|
| char/varchar/text | varchar |
| nchar/nvarchar/ntext | nvarchar |
| binary/varbinary/image | varbinary |
SUBSTRING関数は指定された文字列の開始位置から文字数分を切り出し編集後の文字列を戻します。漢字は1文字単位で数えられます。PostgreSQLではマルチバイトに対応していない場合はバイト単位で切り出されます。
MySQLでは、SUBSTRING関数はマルチバイトセーフです。また、「開始位置」に負の値を使用することもできます。 その場合、部分文字列の先頭は文字列の先頭でなく、文字列の末尾からの 「開始位置」文字になります。 この関数のどの形式でも、「開始位置」で負の値を使用できます。 「開始位置」の値が 0 の場合、空の文字列が返されます。
補助文字 (サロゲート ペア)
SQL Server（マイクロソフト公式）
補助文字 (SC) の照合順序を使用する場合、「開始位置」 と 「文字数」 では、「対象文字列」 の各サロゲート ペアが 1 文字としてカウントされます。
バイト数で切り取る関数はSQLServerにはありません。
実行例
MySQLの実行例
/* MySQL 文字列の開始位置から文字数分を切り出すサンプル */
SELECT STR,SUBSTRING(STR,2,1) FROM testdb.WORK;
FROM構文を使用した場合
/* MySQL FROMを使用した構文サンプル */
SELECT SUBSTRING('travel planning' FROM 4);
FROM FOR構文を使用した場合
/* MySQL FROM FORを使用した構文サンプル */
SELECT SUBSTRING('travel planning' FROM 4 FOR 2);
特定の文字以降の文字列を切り出す方法
例えば、Eメールアドレスの「@」の右側を切り出すような文字列の中にある特定文字を目印に、その右側や左側の文字を切り出すなどできます。そのためには、特定の文字の位置を調べるMySQLの関数であるinstr()やlocate()を併用します。
/* MySQL 特定の文字以降の文字列を切り出すサンプル */
select 'sunarin@gmail.com', substring('sunarin@gmail.com',instr('sunarin@gmail.com', '@')+1)
括弧内の文字列を切り出す
instr()関数を組み合わせれば、ある文字列の括弧内の文字列を切り出すこともできます。
/* MySQL 括弧内の文字列を切り出すサンプル */
select 'りんご(青森県産)',substring('りんご(青森県産)',instr('りんご(青森県産)', '(')+1,instr('りんご(青森県産)', ')') - instr('りんご(青森県産)', '(') -1) 
SQLServerの実行例
/* SQL Server 文字列の開始位置から文字数分を切り出すサンプル */
SELECT STR,SUBSTRING(STR,2,1) FROM [testdb].[dbo].[WORK];
バイト数で指定する方法
バイト数で指定する場合は、CONVERT関数などの型変換を利用します。
/* SQL Server バイト数で指定するサンプル */
SELECT CONVERT(CHAR(2), 'TEST TEST');
UPDATE文で使用する方法
/* SQL Server UPDATE文で使用するサンプル */
UPDATE [testdb].[dbo].[WORK] SET STR= SUBSTRING(STR, 1, 3);