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?

I agree with Tim that more information on the end-goal is needed. My first thought would be to use a table variable... — Sean
– Sean, Commented May 17, 2019 at 14:37
I would argue that no, you don't really want to do that. This screams of being an xy problem — Sean Lange
– Sean Lange, Commented May 17, 2019 at 14:38
I'd like to insert each float variable into a separate table with specific individual column names for each value in the 5 value comma deliminated string. — Delrog
– Delrog, Commented May 17, 2019 at 14:38
You can store the result in a table parameter and use it in the INSERTs — Panagiotis Kanavos
– Panagiotis Kanavos, Commented May 17, 2019 at 14:48

Panagiotis Kanavos · Accepted Answer · 2019-05-17 14:59:14Z

5

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.

edited May 17, 2019 at 14:59

answered May 17, 2019 at 14:51

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Raymond Nijland Over a year ago

the output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value). source

Raymond Nijland Over a year ago

i wonder now after making mine comment how you would get a deterministic result as the function does not provide a index number it only seams to return a value?

Panagiotis Kanavos Over a year ago

@RaymondNijland you can't really, which is why JSON is probably the best option

Panagiotis Kanavos Over a year ago

@RaymondNijland what's even weirder is that STRING_SPLIT is considered deterministic even though the order isn't guaranteed. Or perhaps the order is changed consistently for different values???

Panagiotis Kanavos Over a year ago

@JohnCappelletti the lack of a JSON type even in 2019 was a rather strong indicator that the XML strategy wasn't .... very performant.

|

John Cappelletti · Accepted Answer · 2019-05-17 14:43:47Z

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

answered May 17, 2019 at 14:43

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

1 Comment

Panagiotis Kanavos Over a year ago

Even easier, JSON. No need for replacements, just surround the string with square brackets

Collectives™ on Stack Overflow

STRING_SPLIT to Multiple Variables

2 Answers 2

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related