T-SQL: 文字列結合の逆操作 - 文字列を複数のレコードに分割する方法

T-SQL: 文字列結合の逆操作 - 文字列を複数のレコードに分割する方法

概要

このチュートリアルでは、T-SQL を用いて文字列を複数のレコードに分割する様々な方法を、詳細な解説とサンプルコードと共に紹介します。

方法

以下の4つの方法を紹介します。

SUBSTRING() 関数は、文字列から部分文字列を抽出するために使用できます。分割したい文字列と区切り文字を指定することで、個々のレコードを取り出すことができます。

例:

-- カンマ区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';
SELECT SUBSTRING(@str, 1, CHARINDEX(',', @str) - 1) AS Value; -- 'a'
SELECT SUBSTRING(@str, CHARINDEX(',', @str) + 1, LEN(@str)) AS Value; -- 'b,c,d,e'

WHILE CHARINDEX(',', @str) > 0
BEGIN
  SELECT SUBSTRING(@str, 1, CHARINDEX(',', @str) - 1) AS Value;
  SET @str = SUBSTRING(@str, CHARINDEX(',', @str) + 1, LEN(@str));
END;

PATINDEX() 関数は、パターンに一致する文字列の位置を見つけるために使用できます。正規表現を用いることで、複雑な区切り文字にも対応できます。

-- 半角スペース区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a b c d e';
SELECT SUBSTRING(@str, 1, PATINDEX('%[ ]%', @str) - 1) AS Value; -- 'a'
SELECT SUBSTRING(@str, PATINDEX('%[ ]%', @str) + 1, LEN(@str)) AS Value; -- 'b c d e'

WHILE PATINDEX('%[ ]%', @str) > 0
BEGIN
  SELECT SUBSTRING(@str, 1, PATINDEX('%[ ]%', @str) - 1) AS Value;
  SET @str = SUBSTRING(@str, PATINDEX('%[ ]%', @str) + 1, LEN(@str));
END;

XML 処理

T-SQL では、XML データを処理するための機能が提供されています。文字列を XMLに変換し、XPath を用いて個々の要素を抽出することができます。

-- カンマ区切りの文字列をXMLに変換し、要素を抽出
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';
DECLARE @xml XML = '<root><item>' + REPLACE(@str, ',', '</item><item>') + '</item></root>';
SELECT t.value('.', 'VARCHAR(50)') AS Value
FROM @xml.nodes('/root/item') AS t;

テーブル関数

分割処理をテーブル関数として実装することで、コードの再利用性と可読性を向上させることができます。

CREATE FUNCTION [dbo].[SplitString]
(
  @str VARCHAR(MAX),
  @delimiter VARCHAR(1)
)
RETURNS TABLE
AS
BEGIN
  DECLARE @pos INT = 1;
  WHILE @pos <= LEN(@str)
  BEGIN
    SELECT SUBSTRING(@str, @pos, CHARINDEX(@delimiter, @str, @pos) - @pos) AS Value;
    SET @pos = CHARINDEX(@delimiter, @str, @pos) + 1;
  END;
END;

SELECT * FROM [dbo].[SplitString]('a,b,c,d,e', ',');

まとめ

T-SQL では、様々な方法で文字列を分割することができます。それぞれの方法にはメリットとデメリットがあり、状況に応じて適切な方法を選択する必要があります。

上記

-- カンマ区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';

-- 最初のレコード
SELECT SUBSTRING(@str, 1, CHARINDEX(',', @str) - 1) AS Value; -- 'a'

-- 残りのレコード
SELECT SUBSTRING(@str, CHARINDEX(',', @str) + 1, LEN(@str)) AS Value; -- 'b,c,d,e'

-- ループ処理による分割
WHILE CHARINDEX(',', @str) > 0
BEGIN
  -- 最初のレコード
  SELECT SUBSTRING(@str, 1, CHARINDEX(',', @str) - 1) AS Value;

  -- 残りの文字列
  SET @str = SUBSTRING(@str, CHARINDEX(',', @str) + 1, LEN(@str));
END;

PATINDEX() 関数

-- 半角スペース区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a b c d e';

-- 最初のレコード
SELECT SUBSTRING(@str, 1, PATINDEX('%[ ]%', @str) - 1) AS Value; -- 'a'

-- 残りのレコード
SELECT SUBSTRING(@str, PATINDEX('%[ ]%', @str) + 1, LEN(@str)) AS Value; -- 'b c d e'

-- ループ処理による分割
WHILE PATINDEX('%[ ]%', @str) > 0
BEGIN
  -- 最初のレコード
  SELECT SUBSTRING(@str, 1, PATINDEX('%[ ]%', @str) - 1) AS Value;

  -- 残りの文字列
  SET @str = SUBSTRING(@str, PATINDEX('%[ ]%', @str) + 1, LEN(@str));
END;

-- カンマ区切りの文字列をXMLに変換し、要素を抽出
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';
DECLARE @xml XML = '<root><item>' + REPLACE(@str, ',', '</item><item>') + '</item></root>';

SELECT t.value('.', 'VARCHAR(50)') AS Value
FROM @xml.nodes('/root/item') AS t;

-- テーブル関数による分割
CREATE FUNCTION [dbo].[SplitString]
(
  @str VARCHAR(MAX),
  @delimiter VARCHAR(1)
)
RETURNS TABLE
AS
BEGIN
  DECLARE @pos INT = 1;
  WHILE @pos <= LEN(@str)
  BEGIN
    SELECT SUBSTRING(@str, @pos, CHARINDEX(@delimiter, @str, @pos) - @pos) AS Value;
    SET @pos = CHARINDEX(@delimiter, @str, @pos) + 1;
  END;
END;

-- テーブル関数の呼び出し
SELECT * FROM [dbo].[SplitString]('a,b,c,d,e', ',');

補足:

• 上記のコードは、SQL Server 2005 以降で使用できます。
• コードを実行する前に、必要に応じてテーブルや変数を事前に作成してください。
• サンプルコードはあくまでも参考例です。必要に応じて修正や変更を加えてください。

T-SQLで文字列を分割するその他の方法

FOR XML PATH() 構文は、XML データを生成するための構文ですが、文字列分割にも利用できます。

-- カンマ区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';
SELECT Value
FROM (
  SELECT @str AS str
  FOR XML PATH('')
) AS t
CROSS APPLY STRING_SPLIT(t.str, ',');

CLR (Common Language Runtime)

CLR を使用して、C# などの .NET 言語で分割処理を実装することができます。

-- C# コード
public static IEnumerable<string> SplitString(string str, string delimiter)
{
  return str.Split(delimiter.ToCharArray());
}

-- T-SQL コード
CREATE ASSEMBLY [MyAssembly]
FROM 'C:\path\to\assembly.dll';

SELECT s.value
FROM [MyAssembly].[dbo].[SplitString]('a,b,c,d,e', ',') AS t
CROSS APPLY OPENJSON(t) WITH (value AS s);

レガシー関数

SQL Server 2005 以前のバージョンの場合は、以下のレガシー関数を利用できます。

• LTRIM()
• CHARINDEX()

これらの関数を組み合わせて、文字列を分割することができます。

-- カンマ区切りの文字列を分割
DECLARE @str VARCHAR(50) = 'a,b,c,d,e';

-- 最初のレコード
SELECT LTRIM(RTRIM(SUBSTRING(@str, 1, CHARINDEX(',', @str) - 1))) AS Value; -- 'a'

-- 残りのレコード
DECLARE @pos INT = CHARINDEX(',', @str) + 1;
WHILE @pos <= LEN(@str)
BEGIN
  SELECT LTRIM(RTRIM(SUBSTRING(@str, @pos, CHARINDEX(',', @str, @pos) - @pos))) AS Value;
  SET @pos = CHARINDEX(',', @str, @pos) + 1;
END;

その他のツール

• SSIS (SQL Server Integration Services)
• PowerShell

これらのツールを使用して、文字列分割処理を行うこともできます。