T-SQLで文字列操作をマスターしよう！ STRING_SPLIT 関数、SUBSTRING_INDEX 関数、STUFF 関数、PATINDEX 関数、SUBSTRING 関数、XML 処理などを徹底解説

T-SQLにおける文字列分割関数：詳細解説

T-SQL（Transact-SQL）は、Microsoft SQL Serverで使用されるデータベース言語です。文字列操作はデータ分析やレポート作成において重要であり、T-SQLには様々な文字列処理関数があります。その中でも、文字列を分割する関数は頻繁に使用されます。

本記事では、T-SQLにおける主要な文字列分割関数である STRING_SPLIT 関数と SUBSTRING_INDEX 関数について、詳細な解説と比較を行います。さらに、それぞれの関数の応用例も紹介し、状況に応じた適切な関数の選択を支援します。

STRING_SPLIT 関数

STRING_SPLIT 関数は、SQL Server 2016 で導入された比較的新しい関数です。指定した区切り文字に基づいて、文字列を複数の行に分割し、テーブル値として返します。構文は以下の通りです。

SELECT *
FROM STRING_SPLIT(input_string, delimiter);

引数

• input_string: 分割対象の文字列
• delimiter: 区切り文字

戻り値

• 各部分文字列を含むテーブル

例

次の例では、カンマ区切りの文字列を分割し、各要素を新しい行に表示します。

SELECT *
FROM STRING_SPLIT('りんご,ぶどう,みかん', ',');

結果：

りんご
ぶどう
みかん

オプション

STRING_SPLIT 関数は、オプション引数を使用して以下の機能を提供します。

• 区切り文字の先頭/末尾のトリミング
• 空白要素の処理
• 出力列の順序制御

SUBSTRING_INDEX 関数

SUBSTRING_INDEX 関数は、SQL Server 2000 以降で使用可能な関数です。指定した文字列から、最大1回だけ出現する区切り文字までの部分文字列を返します。構文は以下の通りです。

SELECT SUBSTRING_INDEX(input_string, delimiter, occurrence);

• occurrence: 区切り文字の出現回数（1から始まる整数。デフォルトは1）

• 区切り文字までの部分文字列

次の例では、最初のカンマまでの部分文字列のみを抽出します。

SELECT SUBSTRING_INDEX('りんご,ぶどう,みかん', ',', 1);

結果：

りんご

関数比較

応用例

• CSVデータの処理: STRING_SPLIT 関数を使用して、CSVデータの各列を個別の行に分割できます。
• URLの解析: SUBSTRING_INDEX 関数を使用して、URLからホスト名、パス、クエリ文字列などを抽出できます。
• 文字列の連結と分割の繰り返し: 複数の区切り文字を含む複雑な文字列を処理する場合は、STRING_SPLIT 関数を繰り返し使用するのが効率的です。
• 単純な文字列抽出: 特定の区切り文字までの部分文字列のみを抽出する場合は、SUBSTRING_INDEX 関数のほうが高速でシンプルです。

STRING_SPLIT 関数は、柔軟性と機能性に優れていますが、処理速度がやや遅いという欠点があります。一方、SUBSTRING_INDEX 関数は処理速度が速いですが、機能が限定されています。

状況に応じて適切な関数を選択することが重要です。複雑な文字列処理や繰り返し処理には STRING_SPLIT 関数、単純な文字列抽出には SUBSTRING_INDEX 関数を使用することをおすすめします。

• Microsoft 公式ドキュメント:

-- CSVデータのサンプル
DECLARE @csv_data NVARCHAR(MAX) = '名前,年齢,住所
山田太郎,30,東京都渋谷区
佐藤花子,25,神奈川県横浜市';

-- CSVデータを分割してテーブルに格納
SELECT *
INTO #tbl_csv
FROM STRING_SPLIT(@csv_data, ',');

-- 分割結果を確認
SELECT * FROM #tbl_csv;

DROP TABLE #tbl_csv;

2 URLの解析

-- URLのサンプル
DECLARE @url NVARCHAR(MAX) = 'https://www.example.com/path/to/resource?param1=value1&param2=value2';

-- ホスト名、パス、クエリ文字列を抽出
SELECT
    SUBSTRING_INDEX(@url, '/', 1) AS hostname,
    SUBSTRING_INDEX(@url, '?', 1) AS path,
    SUBSTRING_INDEX(@url, '?', -1) AS query_string;

1 特定の区切り文字までの部分文字列抽出

-- サンプル文字列
DECLARE @text NVARCHAR(MAX) = 'りんご,ぶどう,みかん,メロン,スイカ';

-- 最初のカンマまでの部分文字列を抽出
SELECT SUBSTRING_INDEX(@text, ',', 1) AS first_word;

-- 最後のカンマ以降の部分文字列を抽出
SELECT SUBSTRING_INDEX(@text, ',', -1) AS last_word;

2 文字列の連結と分割の繰り返し

-- サンプル文字列
DECLARE @text NVARCHAR(MAX) = '1|2|3|4|5';

-- 区切り文字で分割して、各要素を2つずつ連結
DECLARE @result NVARCHAR(MAX);
SET @result = '';

WHILE PATINDEX('|', @text) > 0
BEGIN
    SET @result = @result + SUBSTRING_INDEX(@text, '|', 1) + ',';
    SET @text = SUBSTRING_INDEX(@text, '|', -1);
END

-- 連結結果を表示
SELECT @result;

STUFF 関数は、文字列の一部を別の文字列で置き換えるために使用できます。この機能を利用して、文字列を分割することもできます。

-- サンプル文字列
DECLARE @text NVARCHAR(MAX) = 'りんご,ぶどう,みかん';

-- カンマを空白に置き換えて分割
SELECT STUFF(@text, PATINDEX(',', @text), LEN(','), '') AS split_text;

PATINDEX と SUBSTRING 関数

PATINDEX 関数は、指定した文字列 (パターン) が別の文字列 (対象文字列) 内に最初に現れる位置を返します。SUBSTRING 関数は、文字列の一部を抽出するために使用できます。これらの関数を組み合わせて、文字列を分割できます。

-- サンプル文字列
DECLARE @text NVARCHAR(MAX) = 'りんご,ぶどう,みかん';

-- カンマで区切られた各部分文字列を抽出
DECLARE @delimiter NVARCHAR(MAX) = ',';
DECLARE @pos INT = 1;
DECLARE @next_pos INT;

WHILE @pos <= LEN(@text)
BEGIN
    -- 次の区切り文字までの部分文字列を抽出
    SET @next_pos = PATINDEX(@delimiter, @text, @pos);
    IF @next_pos = 0
        SET @next_pos = LEN(@text) + 1;

    SELECT SUBSTRING(@text, @pos, @next_pos - @pos) AS split_text;

    -- 次の位置へ移動
    SET @pos = @next_pos;
END

XML 処理

T-SQL には、XML データを処理するための関数があります。XML を介して文字列を表現することで、様々な方法で分割できます。

例:

-- サンプル文字列
DECLARE @text NVARCHAR(MAX) = '<data>りんご,ぶどう,みかん</data>';

-- XML としてパース
DECLARE @xml XML = CONVERT(XML, @text);

-- 各要素を個別の行として抽出
SELECT *
FROM @xml.nodes('/data/*');

CLR（Common Language Runtime）

CLR を使用して、.NET Framework のライブラリを T-SQL プロシージャ内で呼び出すことができます。.NET Framework には、様々な文字列処理クラスが含まれているため、それらを活用して独自の方法で文字列を分割できます。

最適な方法の選択

使用する方法は、状況によって異なります。以下の点を考慮して、最適な方法を選択してください。

• 処理対象の文字列の形式
• 分割の要件
• 処理速度
• 開発者のスキル

T-SQL には、文字列を分割するための様々な方法があります。それぞれの特徴を理解し、状況に応じて適切な方法を選択することが重要です。

• Microsoft 公式ドキュメント: