はじめに
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 列を利用します。
表題のサンプルコード
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 部は省略）
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 行程度繰り返した限りにおいては、うまく動作しました。
ということで、この注意点を踏まえた上で、このコードを参考にしていただけますと幸いです。