STRING_SPLIT（SQL Server）

ITコーディネータのシュウです。

少し前に、娘と一緒に、横浜港の大桟橋国際客船ターミナルから乗船するレストラン船「ロイヤルウイング」に乗って、食事をしながらクルージングを楽しむ機会がありました。大桟橋国際客船ターミナルを出航し、ベイブリッジをくぐり東京湾へ出て、その後再びベイブリッジを通過し横浜港をゆっくり周遊するというもので、90分ほどの内容でしたが、初めてのこともあり、楽しいひと時を過ごすことができました。少し天候がよくなかったというのが残念でしたが、ピアノの生演奏を聴けたり、食事後は外のデッキに出て、海を眺めて過ごすことができます。上の写真は、乗船する前に撮った写真です。
時間も余裕もなかなかないですが、何日間かかけてのクルージングの旅行とか、できたら楽しいでしょうね！

＜本日の題材＞
STRING_SPLIT（SQL Server）

SQL Serverで便利な関数を見つけたので、今回ブログで取り上げてみたいと思います。これは、何らかの区切り文字を含んだ状態でデータが存在しているときに、そのデータを区切り文字で分割して、複数行として値を返すことができます。

例)
衣料品に関する「製品情報」テーブルに「サイズ」「色」という項目があり、それらに、区切り文字を含んで該当するサイズや色が登録されているとします。そのときに、区切り文字を含む項目について、区切り文字で分割して、データを複数行で表示したいときに使用できます。

最初に「製品情報」テーブルを作成して、データを登録します。

CREATE TABLE dbo.製品情報(
製品ID INT,
製品名 NVARCHAR(30),
サイズ NVARCHAR(30),
色 NVARCHAR(30),
CONSTRAINT PK_製品情報 PRIMARY KEY (製品ID));

INSERT INTO dbo.製品情報 VALUES(1, 'Ｔシャツ_001', 'S,M,L,XL,XXL', '赤/青/黄/橙/緑/黒');
INSERT INTO dbo.製品情報 VALUES(2, 'Ｔシャツ_002', 'S,M,L', '青/桃/黄');
INSERT INTO dbo.製品情報 VALUES(3, 'Ｙシャツ_001', 'S,M,L,XL', '白/灰');
INSERT INTO dbo.製品情報 VALUES(4, 'Ｙシャツ_002', 'S,M,L,XL,XXL', '白');
INSERT INTO dbo.製品情報 VALUES(5, 'ポロシャツ_001', 'S,M,L', '赤/青/黒/白');

上記のようにデータを登録した製品情報テーブルについて、データを抽出すると以下になります。

SELECT * FROM [dbo].[製品情報]
ORDER BY 製品ID;

これについて、製品毎、サイズ毎に行を分けて抽出したい場合に、以下のようにすると簡単に抽出が可能となります。
サイズの場合の区切り文字「,」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS サイズ
FROM dbo.製品情報
CROSS APPLY STRING_SPLIT(サイズ, ',');

同様に、製品毎、色毎に行を分けて抽出したい場合には、区切り文字「/」を CROSS APPLY STRING_SPLITの後に、項目名とともに設定します。

SELECT 製品ID, 製品名, value AS 色
FROM dbo.製品情報
CROSS APPLY STRING_SPLIT(色, '/');

また、色に「青」「白」をともにデータとして持つレコードを抽出したい場合に、下記のようなSQLで抽出が可能です。

SELECT 製品ID, 製品名, サイズ, 色
FROM dbo.製品情報
WHERE '青' IN (SELECT value FROM STRING_SPLIT(色, '/'))
AND '白' IN (SELECT value FROM STRING_SPLIT(色, '/'));

同様に、サイズで「XL」「XXL」をともにデータとして持つレコードを抽出したい場合、以下のようにできます。

SELECT 製品ID, 製品名, サイズ, 色
FROM dbo.製品情報
WHERE 'XL' IN (SELECT value FROM STRING_SPLIT(サイズ, ','))
AND 'XXL' IN (SELECT value FROM STRING_SPLIT(サイズ, ','));