STRING_SPLIT to Multiple Variables

Question

I have a string that looks as such:

'1.25,5.34,6.9,8.6547,2.3'

I would like to store each comma deliminated value into variables like so but programmatically in T-SQL:

Declare @Var1 float
Set @Var1 = 1.25
...

@Var2 = 5.34 
@Var3 = 6.9

And so on so forth..

How would I do so?

Answer 1

Convert to JSON

Perhaps the easiest solution would be to convert the string to a JSON array and access the items by position :

declare @text varchar(200)='1.25,5.34,6.9,8.6547,2.3'
declare @json varchar(202)='[' + @text + ']'

declare @var1 numeric(18,5)= JSON_VALUE(@json,'$[0]')
declare @var2 numeric(18,5)= JSON_VALUE(@json,'$[1]')

select @var1,@var2

Store in Table variable but lose the order

A set-based solution would be to store the items in a table variable BUT the order will probably be lost as Raymond Nijland noted :

declare @text varchar(200)='1.25,5.34,6.9,8.6547,2.3'

declare @values table (id int identity,val varchar(20))

insert into @values (val)
select trim(value) from STRING_SPLIT(@text,',') x

insert into SomeTable (value1)
select val 
from @values where ID=1

The only order that can be imposed is ascending or descending using ORDER BY, which assumes the input's order doesn't matter. Not very helpful.

Answer 2

If you have a fixed number of variables, you can use a little XML

Declare @S varchar(max) = '1.25,5.34,6.9,8.6547,2.3'

Declare @Var1 float,@Var2 float,@Var3 float,@Var4 float,@Var5 float

Select @Var1 = n.value('/x[1]','float')
      ,@Var2 = n.value('/x[2]','float')
      ,@Var3 = n.value('/x[3]','float')
      ,@Var4 = n.value('/x[4]','float')
      ,@Var5 = n.value('/x[5]','float')
From  (Select cast('<x>' + replace(@S,',','</x><x>')+'</x>' as xml) as n) X