はじめに

SQL Server 2016 以降で使用できる STRING_SPLIT は便利な関数ですが、分割した値を変数に代入するサンプルを、なかなかネットで見つけられず随分と悩んだので、そのコードを共有します。

STRING_SPLIT の詳細はこちら
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Learn

前提

前提として、STRING_SPLIT の第3の引数 enable_ordinal を有効(1)にすると、分割された部分文字列が並ぶ value 列とともに、ordinal 列（序数）が返ります。

SELECT * FROM STRING_SPLIT('All about SQL,2013/5/20,3800', ',', 1);

結果

value	ordinal
All about SQL	1
2013/5/20	2
3800	3

この ordinal 列を利用します。

表題のサンプルコード

T-SQL

DECLARE @title   varchar(30)
DECLARE @pubdate date
DECLARE @price   int
DECLARE @buf varchar(50) = 'All about SQL,2013/5/20,3800'

SELECT @title   = CAST(value as varchar(30)) FROM STRING_SPLIT(@buf, ',', 1) WHERE ordinal=1;
SELECT @pubdate = CAST(value as date)        FROM STRING_SPLIT(@buf, ',', 1) WHERE ordinal=2;
SELECT @price   = CAST(value as int )        FROM STRING_SPLIT(@buf, ',', 1) WHERE ordinal=3;

これで各々の変数に、分割された値が型変換されて入ります。
しかし、コードが冗長なので、 SELECT 一本にまとめます。（ DECLARE 部は省略）

T-SQL

SELECT
	@title   = CASE ordinal WHEN 1 THEN CAST(value as varchar(20)) ELSE @title   END
,	@pubdate = CASE ordinal WHEN 2 THEN CAST(value as date)        ELSE @pubdate END
,	@price   = CASE ordinal WHEN 3 THEN CAST(value as int)         ELSE @price   END
FROM STRING_SPLIT(@buf, ',', 1);

SELECT @title as [題名], @pubdate as [発売日], @price as [価格];

結果

題名	発売日	価格
All about SQL	2013-05-20	3800

実際のコードでは、 value を TRIM で囲って余分な空白があれば除去するとか、
CAST ではなく TRY_CAST を使って安全に型変換するとかが必要になると思います。

最後に注意点

なお、最後になりましたが、先の Microsoft の STRING_SPLIT 説明ページに、気になる一文があります。

出力行には任意の順序を指定できます。この順序が入力文字列の部分文字列の順序と一致するかどうかは保証されません。

これが、分割された部分文字列の順序と ordinal 列の序数の値が必ずしも一致しない（左端から 1, 2, 3, ... とならない場合もある）ことを指すのか、それとも、 SELECT から返される結果の順序が順不同であることを指すのか、私にははっきりと読み取れません。（どうも前者っぽい気はしますが。）

ただ、私がこのサンプルコードをテストしたり、この例のように 1 行の入力文字列を 3 分割して変数に代入する処理を 300 行程度繰り返した限りにおいては、うまく動作しました。

ということで、この注意点を踏まえた上で、このコードを参考にしていただけますと幸いです。