SQL Server SUBSTRING, LEFT, RIGHTの使い方徹底解説

SQL Server SUBSTRING, LEFT, RIGHTの使い方徹底解説：文字列操作の基本から応用まで

はじめに：なぜSQL Serverの文字列関数は重要か

データベースにおけるデータは、数値や日付だけでなく、テキストデータも非常に大きな割合を占めています。氏名、住所、商品名、説明文、コード、ログデータなど、様々な情報が文字列として格納されています。これらの文字列データから必要な部分だけを抽出したり、特定の部分を加工したりする処理は、データ分析、レポート作成、データクレンジング、アプリケーション開発など、多くの場面で不可欠となります。

SQL Serverには、文字列を操作するための豊富な組み込み関数が用意されています。中でも、文字列から特定の位置の文字を取り出す関数は、最も基本的かつ頻繁に使用されるものの一つです。本記事では、その中でも特に重要なSUBSTRING、LEFT、RIGHTという3つの関数に焦点を当て、その基本的な使い方から詳細な挙動、エラー処理、NULLの扱い、さらには他の関数との組み合わせによる応用例、パフォーマンスに関する注意点まで、徹底的に解説します。

これらの関数をマスターすることは、SQL Serverを使ったデータ処理の幅を大きく広げることにつながります。約5000語にわたる詳細な解説を通じて、これらの関数を深く理解し、自信を持って使いこなせるようになることを目指しましょう。

1. SUBSTRING関数：任意の位置から任意長の文字列を抽出する

SUBSTRING関数は、指定した文字列の中から、指定した開始位置から指定した長さの部分文字列を取り出す最も汎用的な関数です。

1.1. 基本構文

sql
SUBSTRING ( expression , start , length )

• expression:
  • 部分文字列を取り出す対象となる文字列式です。
  • VARCHAR, NVARCHAR, VARBINARY, NVARBINARY, TEXT, NTEXT, IMAGEデータ型、または暗黙的にこれらの型に変換可能なデータ型を指定できます。
  • 数値型や日付型などの値を指定した場合、暗黙的に文字列型に変換されてから処理されます。ただし、明示的にCASTまたはCONVERT関数で文字列型に変換することをお勧めします。
• start:
  • 部分文字列の抽出を開始する位置を整数で指定します。
  • 重要な点として、SQL Serverの文字列の開始位置は1から始まります。 多くのプログラミング言語（C#, Java, Pythonなど）では0から始まるため、注意が必要です。
  • startが1の場合、文字列の最初の文字から抽出が開始されます。
• length:
  • 抽出する部分文字列の長さを整数で指定します。つまり、startで指定された位置から数えて何文字（または何バイト）取り出すかを指定します。

1.2. 引数の詳細と挙動

• expression:
  • VARCHARやNVARCHARの場合、startとlengthは文字数を指定します。
  • VARBINARYやIMAGEの場合、startとlengthはバイト数を指定します。
  • TEXTやNTEXTの場合も、同様に文字数またはバイト数を指定しますが、これらのデータ型は非推奨であり、代わりにVARCHAR(MAX)やNVARCHAR(MAX)を使用することが推奨されます。
• start:
  • 1ベースインデックス: 繰り返しになりますが、開始位置は1です。例えば、文字列の2番目の文字から抽出したい場合はstartに2を指定します。
  • startがexpressionの文字列長（LEN(expression)またはDATALENGTH(expression)）より大きい場合、結果は空文字列が返されます。エラーにはなりません。
  • startが1未満（0または負の値）の場合、エラーが発生します。
• length:
  • lengthが0の場合、結果は空文字列が返されます。
  • lengthが負の値の場合、エラーが発生します。
  • lengthがstartからexpressionの末尾までの文字数（またはバイト数）よりも大きい場合、startから末尾までのすべての文字（またはバイト）が抽出されます。つまり、指定した長さよりも文字列が短くてもエラーにはならず、取り切れるだけ取り出します。

1.3. 具体的な使用例

様々なデータ型やシナリオでのSUBSTRING関数の使い方を見ていきましょう。

例1：基本的な文字列からの抽出

sql
SELECT SUBSTRING('SQL Server Tutorial', 5, 6);
-- 結果: Server
-- 解説: 5番目の文字 'S' から始めて、6文字 ('Server') を抽出します。

例2：開始位置と長さの指定

sql
SELECT SUBSTRING('SQL Server Tutorial', 1, 3); -- 'SQL'
SELECT SUBSTRING('SQL Server Tutorial', 11, 9); -- 'Tutorial'
SELECT SUBSTRING('SQL Server Tutorial', 11, 20); -- 'Tutorial' (lengthが長すぎるがエラーにならない)
SELECT SUBSTRING('SQL Server Tutorial', 20, 5); -- '' (startが文字列長より大きい)

例3：数値型からの抽出 (暗黙的な変換)

sql
SELECT SUBSTRING(1234567890, 3, 4);
-- 結果: 3456
-- 解説: 1234567890は自動的に文字列 '1234567890' に変換されてから処理されます。

例4：日付型からの抽出 (明示的な変換推奨)

日付型を文字列に変換し、特定の部分（例：年、月、日）を取り出す場合によく使用されます。

“`sql
SELECT SUBSTRING(CONVERT(VARCHAR, GETDATE(), 112), 5, 2); — 現在日付の月 (YYMMDD形式から)
— CONVERTスタイル112は ‘YYYYMMDD’ 形式です。
— 例: 現在が2023年10月26日なら ‘20231026’ となり、5番目の文字 ‘1’ から2文字で ’10’ を抽出します。

SELECT SUBSTRING(CONVERT(VARCHAR, GETDATE(), 120), 1, 4); — 現在日付の年 (YYYY-MM-DD HH:MI:SS形式から)
— CONVERTスタイル120は ‘YYYY-MM-DD HH:MI:SS’ 形式です。
— 例: 現在が2023-10-26 10:30:00なら ‘2023-10-26 10:30:00.000’ となり、1番目の文字 ‘2’ から4文字で ‘2023’ を抽出します。
``
このように、日付型を文字列として扱いSUBSTRINGを使用することは可能ですが、日付の各部分を取り出すにはYEAR,MONTH,DAY関数など、日付関数を使用する方が一般的で推奨されます。しかし、特定の形式にフォーマットされた日付文字列から、日付関数では直接取得できない部分を取り出す場合にSUBSTRING`が役立つことがあります。

例5：VARBINARYからの抽出

バイナリデータから特定バイト数を抽出する場合です。

“`sql
— サンプルのVARBINARYデータを作成
DECLARE @binaryData VARBINARY(MAX) = 0x0102030405060708090A;

SELECT SUBSTRING(@binaryData, 3, 4);
— 結果: 0x03040506
— 解説: 3番目のバイトから始めて、4バイトを抽出します。
“`

例6：変数を使用した抽出

開始位置や長さを変数で指定することも可能です。

“`sql
DECLARE @text NVARCHAR(100) = ‘SQL Server is powerful.’;
DECLARE @start INT = 5;
DECLARE @length INT = 6;

SELECT SUBSTRING(@text, @start, @length);
— 結果: Server
“`

1.4. エラーケースの詳細

SUBSTRING関数でエラーが発生するのは、startまたはlengthに無効な値が指定された場合です。

“`sql
— startが0の場合
SELECT SUBSTRING(‘abc’, 0, 1);
— 結果: エラー (Invalid length parameter passed to the LEFT or SUBSTRING function.)

— startが負の場合
SELECT SUBSTRING(‘abc’, -1, 1);
— 結果: エラー (Invalid length parameter passed to the LEFT or SUBSTRING function.)

— lengthが負の場合
SELECT SUBSTRING(‘abc’, 1, -1);
— 結果: エラー (Invalid length parameter passed to the LEFT or SUBSTRING function.)
``
これらのエラーは、startが1未満、またはlengthが0未満の場合に発生します。それ以外の、startが文字列長を超える場合や、lengthがstart`から末尾までの長さより大きい場合は、エラーではなく空文字列または末尾までの文字列が返されます。

1.5. NULLの扱い

SUBSTRING関数では、いずれかの引数がNULLの場合、結果はNULLになります。

sql
SELECT SUBSTRING('abc', 1, NULL); -- 結果: NULL
SELECT SUBSTRING('abc', NULL, 1); -- 結果: NULL
SELECT SUBSTRING(NULL, 1, 1); -- 結果: NULL
SELECT SUBSTRING(NULL, NULL, NULL); -- 結果: NULL
これは、SQL Serverの多くの関数に共通するNULLの挙動です。NULLは「不明な値」を表すため、不明な情報（開始位置や長さ、元の文字列自体）が一つでもある場合、結果も不明 (=NULL) となります。クエリでSUBSTRINGの結果を扱う際には、NULLが発生する可能性を考慮し、必要に応じてISNULLやCOALESCE関数で処理する必要があります。

1.6. パフォーマンスに関する基本的な考慮事項

SUBSTRING関数をWHERE句で列に対して使用する場合、その列に作成されているインデックスが効果的に利用されにくくなることがあります。これは、SUBSTRING関数の計算結果に基づいてフィルタリングが行われるため、データベースシステムがインデックスの順序を利用して効率的に行を絞り込むことが難しくなるためです。

sql
-- 例: ProductCode列の先頭3文字が'ABC'の製品を検索
-- このクエリは、ProductCode列にインデックスがあってもインデックススキャンやフルスキャンになる可能性が高い
SELECT *
FROM Products
WHERE SUBSTRING(ProductCode, 1, 3) = 'ABC';
このような場合、代わりにLIKE演算子を使用することを検討してください。

sql
-- 同じ検索だが、LIKE演算子を使用
-- こちらは、ProductCode列にインデックスがあればインデックスシークが利用される可能性が高い
SELECT *
FROM Products
WHERE ProductCode LIKE 'ABC%';
LIKE 'pattern%' の形式は、インデックスの先頭部分を利用できるため、SARG (Search Argument) として扱われ、パフォーマンスが向上することが期待できます。ただし、LIKE '%pattern' や LIKE '%pattern%' の形式は、インデックスの利用が難しくなります。

より複雑な抽出条件や、頻繁に特定のサブストリングで検索する必要がある場合は、計算列（Computed Column）としてSUBSTRINGの結果を物理的に保存し、その計算列にインデックスを作成するという方法も検討できます。これについては、後述の応用例やパフォーマンスのセクションで詳しく解説します。

2. LEFT関数：文字列の左（先頭）から指定長の文字列を抽出する

LEFT関数は、指定した文字列の左端（先頭）から、指定した文字数の部分文字列を取り出す関数です。SUBSTRING関数でstartに1を指定した場合と等価な処理を行いますが、LEFT関数を使うことで、コードの意図がより明確になります。

2.1. 基本構文

sql
LEFT ( character_expression , integer_expression )

• character_expression:
  • 部分文字列を取り出す対象となる文字列式です。
  • VARCHAR, NVARCHAR, VARBINARY, NVARBINARY, TEXT, NTEXTデータ型、または暗黙的にこれらの型に変換可能なデータ型を指定できます。
  • SUBSTRINGと同様、数値型や日付型などを指定した場合、暗黙的に文字列型に変換されますが、明示的な変換が推奨されます。
• integer_expression:
  • 左端から抽出する文字数（またはバイト数）を整数で指定します。

2.2. 引数の詳細と挙動

• character_expression:
  • VARCHARやNVARCHARの場合、integer_expressionは文字数を指定します。
  • VARBINARYの場合、integer_expressionはバイト数を指定します。
• integer_expression:
  • 抽出する文字数/バイト数: 左端から指定した数だけ文字（またはバイト）を取り出します。
  • integer_expressionが負の値の場合、エラーが発生します。
  • integer_expressionが0の場合、結果は空文字列が返されます。
  • integer_expressionがcharacter_expressionの文字列長（LEN(character_expression)またはDATALENGTH(character_expression)）より大きい場合、character_expression全体が返されます。SUBSTRINGのlength引数と同様に、指定した長さよりも文字列が短くてもエラーにはならず、文字列全体が返されます。

2.3. 具体的な使用例

LEFT関数の使い方を見ていきましょう。

例1：基本的な文字列からの抽出

sql
SELECT LEFT('SQL Server Tutorial', 3);
-- 結果: SQL
-- 解説: 左端から3文字 ('SQL') を抽出します。

例2：様々な長さの指定

sql
SELECT LEFT('SQL Server Tutorial', 0); -- '' (長さ0)
SELECT LEFT('SQL Server Tutorial', 10); -- 'SQL Server'
SELECT LEFT('SQL Server Tutorial', 30); -- 'SQL Server Tutorial' (長さが文字列長より長い)

例3：NULLの扱い

LEFT関数では、character_expressionがNULLの場合、結果はNULLになります。integer_expressionがNULLの場合もNULLになります。

sql
SELECT LEFT(NULL, 3); -- 結果: NULL
SELECT LEFT('abc', NULL); -- 結果: NULL
SELECT LEFT(NULL, NULL); -- 結果: NULL

例4：SUBSTRINGとの等価性

LEFT(string, length)は、SUBSTRING(string, 1, length)と完全に等価です。

sql
SELECT LEFT('abcdef', 3); -- 結果: abc
SELECT SUBSTRING('abcdef', 1, 3); -- 結果: abc
両者は同じ結果を返しますが、左端からの抽出という意図を伝える上ではLEFT関数の方がより明確です。

2.4. エラーケースの詳細

LEFT関数でエラーが発生するのは、integer_expressionに無効な値が指定された場合です。

sql
-- integer_expressionが負の場合
SELECT LEFT('abc', -1);
-- 結果: エラー (Invalid length parameter passed to the LEFT or SUBSTRING function.)
integer_expressionが0の場合はエラーではなく空文字列、文字列長より大きい場合はエラーではなく文字列全体が返されます。

2.5. パフォーマンスに関する注意点

LEFT関数をWHERE句で使用する場合も、SUBSTRINGと同様にインデックスの利用を妨げることがあります。

“`sql
— ProductsテーブルのProductNameの先頭5文字が’Laptop’の製品を検索
— インデックス利用の効率が低下する可能性
SELECT *
FROM Products
WHERE LEFT(ProductName, 5) = ‘Laptop’;

— LIKE演算子での代替。こちらの方がインデックスが利用されやすい
SELECT *
FROM Products
WHERE ProductName LIKE ‘Laptop%’;
``
パフォーマンスに関する考慮事項や、計算列での利用についてもSUBSTRING`と同様です。

3. RIGHT関数：文字列の右（末尾）から指定長の文字列を抽出する

RIGHT関数は、指定した文字列の右端（末尾）から、指定した文字数の部分文字列を取り出す関数です。ファイル名の拡張子を取り出す際などによく使用されます。

3.1. 基本構文

sql
RIGHT ( character_expression , integer_expression )

• character_expression:
  • 部分文字列を取り出す対象となる文字列式です。
  • VARCHAR, NVARCHAR, VARBINARY, NVARBINARY, TEXT, NTEXTデータ型、または暗黙的にこれらの型に変換可能なデータ型を指定できます。
  • SUBSTRING, LEFTと同様、数値型や日付型などを指定した場合、暗黙的に文字列型に変換されますが、明示的な変換が推奨されます。
• integer_expression:
  • 右端から抽出する文字数（またはバイト数）を整数で指定します。

3.2. 引数の詳細と挙動

• character_expression:
  • VARCHARやNVARCHARの場合、integer_expressionは文字数を指定します。
  • VARBINARYの場合、integer_expressionはバイト数を指定します。
• integer_expression:
  • 抽出する文字数/バイト数: 右端から指定した数だけ文字（またはバイト）を取り出します。
  • integer_expressionが負の値の場合、エラーが発生します。
  • integer_expressionが0の場合、結果は空文字列が返されます。
  • integer_expressionがcharacter_expressionの文字列長（LEN(character_expression)またはDATALENGTH(character_expression)）より大きい場合、character_expression全体が返されます。LEFT関数と同様に、指定した長さよりも文字列が短くてもエラーにはならず、文字列全体が返されます。

3.3. 具体的な使用例

RIGHT関数の使い方を見ていきましょう。

例1：基本的な文字列からの抽出

sql
SELECT RIGHT('SQL Server Tutorial', 9);
-- 結果: Tutorial
-- 解説: 右端から9文字 ('Tutorial') を抽出します。

例2：様々な長さの指定

sql
SELECT RIGHT('SQL Server Tutorial', 0); -- '' (長さ0)
SELECT RIGHT('SQL Server Tutorial', 6); -- 'torial'
SELECT RIGHT('SQL Server Tutorial', 30); -- 'SQL Server Tutorial' (長さが文字列長より長い)

例3：NULLの扱い

RIGHT関数も、LEFT関数と同様に、いずれかの引数がNULLの場合、結果はNULLになります。

sql
SELECT RIGHT(NULL, 3); -- 結果: NULL
SELECT RIGHT('abc', NULL); -- 結果: NULL
SELECT RIGHT(NULL, NULL); -- 結果: NULL

例4：SUBSTRINGでの代替表現

RIGHT(string, length)をSUBSTRINGで表現するには、文字列の長さから抽出したい長さを引いた値に1を足した位置を開始位置として指定します。

sql
-- LEFT(string, length) は SUBSTRING(string, 1, length)
-- RIGHT(string, length) は SUBSTRING(string, LEN(string) - length + 1, length)
-- 例:
SELECT RIGHT('abcdef', 3); -- 結果: def
SELECT LEN('abcdef'); -- 6
SELECT SUBSTRING('abcdef', 6 - 3 + 1, 3); -- SUBSTRING('abcdef', 4, 3) -> def
このように、RIGHT関数はLEN関数とSUBSTRING関数を組み合わせることで同じ結果を得られますが、RIGHT関数を使う方がコードの目的が明確になり、簡潔に記述できます。ただし、LEN関数を使用する代替表現は、元の文字列がNULLや空文字列の場合に注意が必要です。LEN(NULL)はNULL、LEN('')は0を返します。SUBSTRINGの開始位置にNULLや1未満の値が渡されるとエラーになるため、RIGHT関数の方が安全に使用できます。

3.4. エラーケースの詳細

RIGHT関数でエラーが発生するのは、integer_expressionに無効な値が指定された場合です。

sql
-- integer_expressionが負の場合
SELECT RIGHT('abc', -1);
-- 結果: エラー (Invalid length parameter passed to the LEFT or SUBSTRING function.)
integer_expressionが0の場合はエラーではなく空文字列、文字列長より大きい場合はエラーではなく文字列全体が返されます。

3.5. パフォーマンスに関する注意点

RIGHT関数をWHERE句で使用する場合も、SUBSTRINGやLEFTと同様にインデックスの利用を妨げることがあります。

“`sql
— ProductsテーブルのProductCodeの末尾3文字が’XYZ’の製品を検索
— インデックス利用の効率が低下する可能性
SELECT *
FROM Products
WHERE RIGHT(ProductCode, 3) = ‘XYZ’;

— LIKE演算子での代替。こちらはインデックスが利用されにくい (先頭からのマッチではないため)
SELECT *
FROM Products
WHERE ProductCode LIKE ‘%XYZ’;
``LIKE ‘%pattern’の形式は、通常はインデックスをフルスキャンするか、インデックススキャンを行うため、パフォーマンスはあまり期待できません。このような末尾一致の検索を頻繁に行う場合は、対象列の逆順の値を格納した列を作成し、その列に対してLEFTやLIKE ‘pattern%’`で検索する、あるいは全文検索インデックスを使用するなどの代替手段を検討する必要があります。

4. SUBSTRING, LEFT, RIGHTの比較と使い分け

これら3つの関数はすべて文字列から部分文字列を抽出する機能を持っていますが、それぞれ得意な場面や使い分けがあります。

関数	構文	抽出位置	抽出方向	用途	SUBSTRINGでの代替	メリット	デメリット
SUBSTRING	SUBSTRING(exp, start, length)	任意	任意	任意の位置から任意長を抽出	N/A	最も汎用的	startが1ベースであることへの注意が必要
LEFT	LEFT(exp, length)	左端（先頭）	右へ	文字列の先頭から指定長を抽出	SUBSTRING(exp, 1, length)	コードの意図が明確で簡潔	右端からの抽出はできない
RIGHT	RIGHT(exp, length)	右端（末尾）	左へ	文字列の末尾から指定長を抽出	SUBSTRING(exp, LEN(exp)-length+1, length)	コードの意図が明確で簡潔	左端からの抽出はできない、LEN代替はNULLに注意

使い分けのポイント：

• 固定の位置から固定長を取り出す場合:
  • 文字列の先頭から N 文字: LEFT(string, N) または SUBSTRING(string, 1, N)。LEFTが推奨。
  • 文字列の末尾から N 文字: RIGHT(string, N). RIGHTが推奨。
  • 文字列の途中（先頭から M 番目の文字）から N 文字: SUBSTRING(string, M, N). SUBSTRINGを使用。
• 区切り文字やパターンに基づいて可変長の部分を取り出す場合:
  • 特定の区切り文字（例: カンマ、ハイフン、スラッシュ）より前、より後、または区切り文字の間隔にある部分を取り出す場合は、CHARINDEXやPATINDEX関数で区切り文字の位置を特定し、その位置情報とLEN関数を組み合わせてSUBSTRING関数のstartやlength引数を計算する必要があります。このような場合はSUBSTRING関数が中心的な役割を果たします。
  • 例: メールアドレスからユーザー名とドメイン名を分離。
  • 例: ファイルパスからファイル名やディレクトリ名を分離。

5. 応用例：他の文字列関数との組み合わせ

SUBSTRING, LEFT, RIGHT関数は、単独で使用されるだけでなく、他の文字列関数と組み合わせて使用されることで、より複雑な文字列操作が可能になります。ここでは、いくつかの代表的な組み合わせ例を紹介します。

5.1. CHARINDEX, PATINDEX, LENとの組み合わせ

最も頻繁に使用される組み合わせです。CHARINDEXまたはPATINDEXで特定の部分文字列やパターンの開始位置を特定し、LEN関数で元の文字列の長さを取得することで、抽出したい部分の開始位置や長さを動的に計算し、SUBSTRINGで取り出します。

• CHARINDEX(substring, expression[, start_location]): expressionの中からsubstringが最初に出現する位置（1ベース）を返します。見つからない場合は0を返します。
• PATINDEX('%pattern%', expression): expressionの中からpatternが最初に出現する開始位置（1ベース）を返します。patternにはワイルドカード（%, _ など）を使用できます。見つからない場合は0を返します。
• LEN(string_expression): 文字列の文字数を返します。末尾のスペースはカウントしません。NVARCHARの場合も文字数を返します。VARCHARの場合も文字数を返しますが、マルチバイト文字セットの場合はバイト数ではなく文字数になります（SQL Server 2008以降）。末尾のスペースを厳密に含めたい場合はDATALENGTHを使用するか、LENの前に+ 'x'などを連結して末尾スペースをなくすテクニックを使います。

例1：メールアドレスからドメイン名を抽出

メールアドレスの形式が [email protected] の場合、@より後ろの部分がドメイン名です。

“`sql
DECLARE @email VARCHAR(100) = ‘[email protected]’;
DECLARE @at_pos INT = CHARINDEX(‘@’, @email);

SELECT
— ユーザー名部分 (@ より前)
LEFT(@email, @at_pos – 1) AS UserName,
— ドメイン名部分 (@ より後)
SUBSTRING(@email, @at_pos + 1, LEN(@email) – @at_pos) AS DomainName;

— 結果例 (メールアドレスによって変わります):
— UserName: example.user
— DomainName: sub.domain.co.jp

— 注意点: @ が含まれていないメールアドレスの場合 CHARINDEX は 0 を返します。
— この場合、LEFT(@email, -1) や SUBSTRING(@email, 1, LEN(@email)) の計算がおかしくなるため、
— CASE 式などで @at_pos が 0 かどうかを確認する必要があります。
``
より堅牢なコードにするには、@at_pos`が0の場合の処理を追加します。

“`sql
DECLARE @email_invalid VARCHAR(100) = ‘invalid.email.address’;
DECLARE @at_pos_invalid INT = CHARINDEX(‘@’, @email_invalid);

SELECT
CASE
WHEN @at_pos_invalid > 0 THEN LEFT(@email_invalid, @at_pos_invalid – 1)
ELSE @email_invalid — @ がない場合はユーザー名全体とするなど
END AS UserName,
CASE
WHEN @at_pos_invalid > 0 THEN SUBSTRING(@email_invalid, @at_pos_invalid + 1, LEN(@email_invalid) – @at_pos_invalid)
ELSE ” — @ がない場合はドメイン名なしとするなど
END AS DomainName;

— 結果例:
— UserName: invalid.email.address
— DomainName:
“`

例2：ファイルパスからファイル名と拡張子を分離

パスの形式が C:\Path\To\FileName.txt の場合、最後の \ より後がファイル名（拡張子含む）、最後の . より後が拡張子です。パス区切り文字は \ と / の両方が使われる可能性があることに注意します。

まず、最後のパス区切り文字の位置を見つけます。REVERSE関数とCHARINDEXを組み合わせるのがよく使われる手法です。

“`sql
DECLARE @filepath NVARCHAR(260) = ‘C:\Users\Documents\Report.2023.final.xlsx’;
DECLARE @slash_pos INT = LEN(@filepath) – CHARINDEX(‘\’, REVERSE(@filepath)) + 1; — 最後の ‘\’ の位置
— または、 ‘/’ も考慮する場合
DECLARE @last_slash_pos INT = IIF(CHARINDEX(‘/’, REVERSE(@filepath)) > CHARINDEX(‘\’, REVERSE(@filepath)),
LEN(@filepath) – CHARINDEX(‘/’, REVERSE(@filepath)) + 1,
LEN(@filepath) – CHARINDEX(‘\’, REVERSE(@filepath)) + 1);

— ファイル名 (最後の ‘\’ より後)
SELECT SUBSTRING(@filepath, @last_slash_pos + 1, LEN(@filepath) – @last_slash_pos) AS FileNameWithExtension;
— 結果例: Report.2023.final.xlsx

— ファイル拡張子 (最後の ‘.’ より後)。ファイル名がない場合や ‘.’ がない場合を考慮
DECLARE @filename NVARCHAR(260) = SUBSTRING(@filepath, @last_slash_pos + 1, LEN(@filepath) – @last_slash_pos);
DECLARE @dot_pos_in_filename INT = CHARINDEX(‘.’, @filename);

SELECT
CASE
WHEN @dot_pos_in_filename > 0 THEN SUBSTRING(@filename, @dot_pos_in_filename + 1, LEN(@filename) – @dot_pos_in_filename)
ELSE ” — ドットがない場合は拡張子なし
END AS FileExtension;
— 結果例: xlsx
``
上記の例では、最後の` の位置を特定するために REVERSE を使いました。REVERSE 後の \ の位置は、元の文字列の末尾から数えた \ の位置になります。全体の長さからこの位置を引くことで、元の文字列の先頭からの位置がわかります。例えば、長さ10の文字列で REVERSE 後の '\' が3番目にある場合、元の文字列では末尾から3番目、つまり先頭からは 10 – 3 + 1 = 8 番目にあることになります。

例3：特定の区切り文字で区切られた文字列からN番目の要素を抽出

例えば、'Apple,Banana,Cherry' という文字列から2番目の要素 'Banana' を取り出す場合です。これは少し複雑で、最初の , の位置と2番目の , の位置を特定する必要があります。

“`sql
DECLARE @list VARCHAR(100) = ‘Apple,Banana,Cherry,Date’;
DECLARE @delimiter CHAR(1) = ‘,’;
DECLARE @element_index INT = 2; — 取り出したい要素の番号 (1ベース)

— 一時的にカンマを特定の文字列に置き換えて処理を容易にする方法 (より簡単な場合)
— 例: カンマを ‘<>’ に置き換えて、XMLやJSON関数でパースするなど、よりモダンな方法もありますが、ここでは文字列関数のみで実現を試みます。

— 特定の要素を抽出するのは、SUBSTRINGとCHARINDEXを繰り返すか、再帰CTE、または新しい文字列関数 SPLIT_STRING を使うのが一般的です。
— SQL Server 2016以降では SPLIT_STRING が利用可能です。
— SELECT value FROM STRING_SPLIT(@list, @delimiter) … (この例は簡単すぎるが)

— SPLIT_STRING がない場合 (古いバージョン)
— 2番目の要素を取り出すには: 最初のカンマの後から、2番目のカンマの前まで

DECLARE @first_delim_pos INT = CHARINDEX(@delimiter, @list);
— 最初のカンマ以降の部分文字列
DECLARE @rest_of_list VARCHAR(100) = SUBSTRING(@list, @first_delim_pos + 1, LEN(@list) – @first_delim_pos);
— その部分文字列の中から次のカンマの位置を見つける
DECLARE @second_delim_pos_in_rest INT = CHARINDEX(@delimiter, @rest_of_list);

SELECT
CASE
WHEN @first_delim_pos > 0 AND @second_delim_pos_in_rest > 0 THEN
LEFT(@rest_of_list, @second_delim_pos_in_rest – 1) — 2番目の要素は最初のカンマから2番目のカンマの間
WHEN @first_delim_pos > 0 AND @second_delim_pos_in_rest = 0 THEN
@rest_of_list — 2番目のカンマがない場合は、最初のカンマ以降の全てが2番目の要素 (最後の要素)
ELSE ” — 最初のカンマがない場合など
END AS SecondElement;

— 結果: Banana
``
このように、区切り文字で分割されたN番目の要素を取り出すのは、SUBSTRING,CHARINDEX,LENを組み合わせることで可能ですが、要素の数が増えるとコードが複雑になります。SQL Server 2016以降であれば、STRING_SPLIT`関数を使用するのが最も簡単で推奨される方法です。

5.2. LTRIM, RTRIMとの組み合わせ

抽出した部分文字列に前後の空白が含まれている場合、LTRIM (左側の空白を削除) や RTRIM (右側の空白を削除) 関数と組み合わせて使用することがよくあります。

“`sql
DECLARE @padded_string VARCHAR(50) = ‘ Some Text with Spaces ‘;
— 先頭から10文字を抽出 (前後の空白を含む)
SELECT SUBSTRING(@padded_string, 3, 10); — 結果: Some Text

— 前後の空白を取り除いてから先頭から10文字を抽出
SELECT LEFT(LTRIM(RTRIM(@padded_string)), 10); — 結果: Some Text
— LTRIMとRTRIMを適用すると ‘Some Text with Spaces’ になります。
— その後 LEFT(, 10) で ‘Some Text ‘ になり、さらに外部で RTRIM() すれば ‘Some Text’ になります。
— 通常は抽出した結果に対してトリムします。
SELECT LTRIM(RTRIM(SUBSTRING(@padded_string, 3, 10))); — 結果: Some Text

— 前後の空白を取り除いてから、空白で区切られた最初の単語を抽出する場合など
— まず空白の位置を見つける
DECLARE @trimmed_string VARCHAR(50) = LTRIM(RTRIM(@padded_string)); — ‘Some Text with Spaces’
DECLARE @space_pos INT = CHARINDEX(‘ ‘, @trimmed_string);

SELECT
CASE
WHEN @space_pos > 0 THEN LEFT(@trimmed_string, @space_pos – 1)
ELSE @trimmed_string — 空白がない場合は文字列全体
END AS FirstWord;
— 結果: Some
“`
データ入力ミスなどにより、文字列に不要な空白が含まれていることはよくあります。正確な部分文字列を抽出するためには、これらのトリミング関数との組み合わせが重要になります。

5.3. REPLACE, STUFFとの組み合わせ

抽出した部分文字列を別の文字列に置き換えたい場合は、REPLACEやSTUFF関数と組み合わせて使用します。

• REPLACE(string_expression, string_pattern, string_replacement): string_expression内のstring_patternの全ての出現箇所をstring_replacementで置き換えます。
• STUFF(character_expression, start, length, character_replacement): character_expressionの中から、start位置からlengthで指定された文字数を削除し、その開始位置にcharacter_replacementを挿入します。

“`sql
— 例: 電話番号 ‘012-345-6789’ の市外局番部分を ‘(012)’ に変更
DECLARE @phone VARCHAR(20) = ‘012-345-6789’;
— 最初の ‘-‘ の位置を見つける
DECLARE @first_hyphen INT = CHARINDEX(‘-‘, @phone);

SELECT
— 方法1: REPLACE を使う (市外局番が固定長の場合など)
REPLACE(LEFT(@phone, @first_hyphen – 1), LEFT(@phone, @first_hyphen – 1), ‘(012)’) + — 市外局番部分を置き換え
SUBSTRING(@phone, @first_hyphen, LEN(@phone) – @first_hyphen + 1); — ‘-‘ 以降はそのまま連結
— 結果: (012)-345-6789

SELECT
— 方法2: STUFF を使う (より正確に指定位置を操作)
STUFF(@phone, 1, @first_hyphen – 1, ‘(012)’); — 1文字目から最初のハイフンの手前までを削除し、'(012)’を挿入
— 結果: (012)-345-6789
``
この例では、LEFTとSUBSTRINGで元の市外局番部分を取り出し、それを新しい値で置き換えて残りの部分と連結する方法と、STUFFで直接指定位置の部分を削除＆挿入する方法を示しています。STUFF`関数は、指定した位置と長さの部分を正確に置き換える際に非常に強力です。

6. パフォーマンスに関する詳細な考察

SUBSTRING, LEFT, RIGHT関数は非常に便利ですが、特に大規模なデータセットに対してこれらの関数を多用する場合、パフォーマンスに大きな影響を与える可能性があります。既に触れたWHERE句でのインデックス非効率性の問題に加え、いくつかの点を考慮する必要があります。

6.1. インデックスとSARG性の破壊

最も重要なのは、これらの関数をWHERE句やJOIN句の条件で、列に対して直接適用する場合です。例えば、WHERE SUBSTRING(ColumnA, 1, 3) = 'ABC'のような条件は、データベースシステムがColumnAのインデックスをそのまま利用して行を絞り込むことができません。代わりに、テーブルの全行、あるいはインデックスの全エントリを読み込み、各行/エントリに対してSUBSTRING関数を計算し、その結果を条件と比較するというプロセスになります。これは、データ量が少ない場合は問題になりませんが、大量のデータがある場合は非常に非効率です（インデックススキャンやフルスキャンと呼ばれる状態）。

SARG (Search Argument) とは、データベースシステムがインデックスを効果的に使用できる形式の条件のことです。ColumnA = 'Value', ColumnA > 10, ColumnA BETWEEN @x AND @y, ColumnA LIKE 'Prefix%' のような形式は一般的にSARG性があり、インデックスシークが利用されやすいです。しかし、Function(ColumnA) = 'Value' や ColumnA + 5 > 10 のように、列に対して関数や演算子を適用した結果を比較する形式は、SARG性が失われることが多いです。

代替策:

• LIKE演算子の利用: 前述の通り、先頭一致検索 (LIKE 'Prefix%') の場合はLEFTやSUBSTRINGよりもLIKEが推奨されます。末尾一致 (LIKE '%Suffix') や中間一致 (LIKE '%Pattern%') の場合は、RIGHTやSUBSTRINGと同様にインデックスが効きにくいですが、場合によってはオプティマイザが工夫する可能性もあります。
• 計算列 (Computed Column) の作成:
  • 頻繁に特定のサブストリングで検索したり、GROUP BYやORDER BYに使用したりする場合は、その部分文字列を格納する計算列を作成し、その計算列にインデックスを作成することを検討できます。
  • 計算列は、他の列の値を計算して得られる仮想的な列です。定義は以下のようになります。
    sql
ALTER TABLE YourTable
ADD SubStringColumn AS SUBSTRING(OriginalColumn, 1, 5);

  • この計算列はデフォルトでは物理的にデータは保存されません（仮想計算列）。インデックスを作成するには、PERSISTEDキーワードを付けて物理的にデータを保存する必要があります。
    “`sql
    ALTER TABLE YourTable
    ADD SubStringColumn PERSISTED AS SUBSTRING(OriginalColumn, 1, 5);

    CREATE INDEX IX_YourTable_SubStringColumn ON YourTable (SubStringColumn);
    ``
*PERSISTED計算列はストレージ容量を消費し、データの挿入・更新・削除のオーバーヘッドが増えますが、WHERE句などでその計算列を使用した場合にインデックスが有効に機能するようになります。ただし、計算列の定義に使用できる関数には制限があり、非決定的関数（実行ごとに結果が変わる関数、例:GETDATE(),NEWID()）は使用できません。SUBSTRING,LEFT,RIGHT,LEN,CHARINDEX,PATINDEX`などは決定的な関数として計算列に使用可能です（一部例外や引数の制限あり）。
    * ETL (Extract, Transform, Load) 処理:
    * トランザクション処理を行うデータベースではなく、分析用のデータウェアハウスなどで大規模なレポートを作成する場合などは、事前にETL処理で必要な部分文字列を別の列として抽出し、物理的に格納しておくことが一般的です。これにより、クエリ実行時の計算負荷をなくし、最適なインデックス戦略を適用できるようになります。
    * 一時テーブルやCTE (Common Table Expression) の活用:
    * 複雑な文字列操作を含むクエリでパフォーマンス問題が発生する場合、まずは元のデータを一時テーブルやCTEに抽出し、その中で文字列操作を行い、その結果に対してフィルタリングや集計を行うといった多段階の処理に分解することで、オプティマイザがより効率的な実行プランを選択できるようになる場合があります。

6.2. 文字コードとLEN/DATALENGTHの違い

SQL Serverでは、文字列データをVARCHAR (非Unicode) とNVARCHAR (Unicode) のデータ型で扱うことができます。また、バイナリデータとしてVARBINARYで扱うこともできます。

• VARCHAR (および非UnicodeのTEXT): 各文字は1バイトで表現されることが多いですが、日本語などのマルチバイト文字セット（MBCS）が使用されている場合は、1文字が2バイト以上になることがあります。LEN関数は文字数を返しますが、古いバージョンのSQL Serverや特定の照合順序設定ではバイト数を返すこともありました（SQL Server 2008以降では文字数）。DATALENGTH関数は常にバイト数を返します。SUBSTRING, LEFT, RIGHTのlength引数は文字数を指定します。
• NVARCHAR (およびUnicodeのNTEXT): 各文字は固定で2バイト（UTF-16）で表現されます。サロゲートペア文字（絵文字など、一部のUnicode文字）は4バイトで表現されます。LEN関数は常に文字数（サロゲートペアは1文字としてカウント）を返します。DATALENGTH関数は常にバイト数（サロゲートペアは4バイトとしてカウント）を返します。SUBSTRING, LEFT, RIGHTのlength引数は文字数を指定します。
• VARBINARY (およびIMAGE): バイト列を格納します。LEN関数はNULLを返します（または非推奨）。DATALENGTH関数はバイト数を返します。SUBSTRING, LEFT, RIGHTのlength引数はバイト数を指定します。

注意点: SUBSTRINGのlength引数は、VARCHARやNVARCHARに対しては「文字数」として解釈されます。VARBINARYに対しては「バイト数」として解釈されます。もしVARCHAR列に対して厳密に「バイト単位」で部分抽出を行いたい場合は、一度VARBINARYにキャストしてからSUBSTRINGを適用する必要があります。

“`sql
— VARCHAR列 (マルチバイト文字セットの場合)
DECLARE @v VARCHAR(20) = ‘あいうえお’; — 5文字、10バイト (Shift_JISの場合)
SELECT LEN(@v); — 結果: 5 (文字数)
SELECT DATALENGTH(@v); — 結果: 10 (バイト数)
SELECT SUBSTRING(@v, 2, 2); — 結果: いう (2文字目から2文字)

— NVARCHAR列 (Unicode)
DECLARE @nv NVARCHAR(20) = ‘あいうえお’; — 5文字、10バイト (UTF-16の場合)
SELECT LEN(@nv); — 結果: 5 (文字数)
SELECT DATALENGTH(@nv); — 結果: 10 (バイト数)
SELECT SUBSTRING(@nv, 2, 2); — 結果: いう (2文字目から2文字)

— VARCHAR列に対してバイト単位でSUBSTRINGしたい場合
DECLARE @v_bytes VARCHAR(20) = ‘あいうえお’;
— 2バイト目から4バイト抽出 (バイト単位で操作)
SELECT CAST(SUBSTRING(CAST(@v_bytes AS VARBINARY(20)), 2, 4) AS VARCHAR(20));
— 結果: い (Shift_JISの場合、2バイト目から4バイトは ‘いう’ の ‘い’ + ‘う’ の前半2バイト)
— このようにバイト単位の操作は文字化けのリスクがあるため、注意が必要です。
``
通常は文字単位での操作で問題ありませんが、特定のプロトコルやファイル形式に合わせて厳密にバイト単位で文字列を扱う必要がある場合は、この違いとDATALENGTHの使用、VARBINARY`へのキャストを理解しておく必要があります。

7. ベストプラクティスと注意点

これらの関数を効果的かつ安全に使用するためのいくつかのベストプラクティスと注意点があります。

• start引数の1ベースインデックスを常に意識する: 多くのプログラミング言語の0ベースインデックスとの違いは混乱の元となりやすいので、特に注意が必要です。
• length引数の挙動を理解する: lengthが文字列長を超えてもエラーにならないこと、0の場合は空文字列になることを理解しておきましょう。負の場合はエラーになります。
• NULL handlingを確実に行う: 引数にNULLが含まれる場合、結果はNULLになります。クエリの途中で予期せずNULLが発生しないか、あるいはNULLが発生した場合の適切な処理（例: ISNULL, COALESCE, CASE 式）を考慮しましょう。

• 引数の検証を行う: 特にユーザー入力や外部システムからのデータに対してこれらの関数を使用する場合、startやlengthの計算結果が不正な値（負の値など）にならないように、事前に検証を行うか、エラーハンドリングを組み込みましょう。
  “`sql
  — 例: NULLや不正な長さを防ぐ
  DECLARE @text VARCHAR(50) = ‘sample’;
  DECLARE @len INT = LEN(@text);
  DECLARE @sub_len INT = 10; — 取り出したい長さ

  SELECT SUBSTRING(@text, 1, CASE WHEN @sub_len < 0 THEN 0 ELSE @sub_len END); — 負の長さを防ぐ
  SELECT SUBSTRING(@text, 1, IIF(@sub_len < 0, 0, @sub_len)); — IIF (SQL Server 2012+)
  SELECT LEFT(@text, CASE WHEN @sub_len < 0 THEN 0 ELSE @sub_len END);
  ``
ただし、SUBSTRINGやLEFT/RIGHTは負のlengthやinteger_expressionでエラーになるため、単純にCASE WHEN @sub_len < 0 THEN 0 ELSE @sub_len ENDとすることで、負の値によるエラーを防ぎ、代わりに空文字列を返すようにできます。
* **可読性の高いコードを書く:** 固定の開始位置や長さを直接数値で指定するのではなく、意味のある変数や、CHARINDEX,LENなどの関数の結果を使用して動的に計算することで、コードの目的が分かりやすくなります。
* **パフォーマンスへの影響を考慮する:** 特にWHERE句やJOIN句でこれらの関数を列に対して使用する場合は、インデックスが利用されにくくなる可能性が高いことを理解し、LIKE演算子や計算列、ETLなどの代替手段を検討しましょう。
* **文字コードの違いを理解する:**VARCHARとNVARCHARでLENとDATALENGTHの結果が異なること、SUBSTRINGのlength`引数は文字数として解釈されることを覚えておきましょう。

8. まとめ

本記事では、SQL Serverにおける文字列操作の基本となるSUBSTRING、LEFT、RIGHTの3つの関数について、その構文、引数の詳細、具体的な使用例、エラーケース、NULLの扱い、他の関数との組み合わせによる応用例、そしてパフォーマンスに関する重要な考慮事項まで、約5000語にわたって徹底的に解説しました。

• SUBSTRINGは最も汎用的で、任意の位置から任意長の文字列を抽出できます。開始位置が1ベースであることに注意が必要です。
• LEFTは文字列の先頭から、RIGHTは文字列の末尾から指定長の文字列を抽出します。それぞれの意図が明確になるため、可能であればSUBSTRINGの代わりにこれらの関数を使用することが推奨されます。
• これらの関数は、CHARINDEX、PATINDEX、LEN、LTRIM、RTRIM、REPLACE、STUFFなどの他の文字列関数と組み合わせて使用することで、より複雑な文字列操作が可能になります。
• WHERE句でこれらの関数を列に適用すると、通常インデックスが効果的に使用されなくなり、パフォーマンスが低下する可能性があります。LIKE演算子での代替や、計算列、ETL処理などのパフォーマンス対策を検討することが重要です。
• NULLの扱いや、VARCHARとNVARCHARにおける文字数/バイト数の違いにも注意が必要です。

これらの関数はSQL Serverでのデータ操作において非常に基礎的かつ強力なツールです。本記事で解説した内容を参考に、実際のクエリ開発やデータ分析でこれらの関数を効果的に活用していただければ幸いです。

参考資料

• Microsoft Docs: SUBSTRING (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver16
• Microsoft Docs: LEFT (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/t-sql/functions/left-transact-sql?view=sql-server-ver16
• Microsoft Docs: RIGHT (Transact-SQL)
  https://learn.microsoft.com/ja-jp/sql/t-sql/functions/right-transact-sql?view=sql-server-ver16
• Microsoft Docs: Working with Strikethrough (SARG)
  https://learn.microsoft.com/en-us/sql/relational-databases/group-policy/walkthrough-working-with-strikethrough-sarg?view=sql-server-ver16 (英語)
• Microsoft Docs: Indexes on Computed Columns
  https://learn.microsoft.com/ja-jp/sql/relational-databases/indexes/indexes-on-computed-columns?view=sql-server-ver16

---

注記: 本記事はSQL Serverの標準的な動作に基づいて解説しています。具体的な挙動やパフォーマンスは、SQL Serverのバージョン、エディション、構成、照合順序、データの特性などによって多少異なる場合があります。実際のシステムで利用される際は、対象の環境での挙動を十分に検証することをお勧めします。