Split string into three variables in SQL Server 2005

Question

I have an string

declare @s varchar(100),
set @s='aaaa,bbbb,cccc'

declare @first varchar(100),
declare @second varchar(100),
declare @third varchar(100)

Now I need to split these strings into three variable holding there values like this

@first=aaaa
@second=bbbb
@third=cccc

If I am using the split function then I get the output like this

aaaa
bbbb
cccc

Is there any better way we can achieve this result? Any help would be appreciated.

I want this result into a variable because I need it for further processing

Thanks

http://stackoverflow.com/questions/2647/split-string-in-sql may be helpful — devdigital, Dec 14 '11 at 19:58

score 3 · Answer 1 · answered Dec 14 '11 at 20:43

Here's a quick and dirty hack, assuming that your input strings always follow that format.

DECLARE @s VARCHAR(100)
SET @s = 'aaaa,bbbb,cccc'


DECLARE @first VARCHAR(100)
DECLARE @second VARCHAR(100)
DECLARE @third VARCHAR(100) 

SET @s = '<row>' + REPLACE(@s, ',', '</row><row>') + '</row>'

SELECT  @first = CONVERT(XML, @s).value('(/row)[1]', 'varchar(100)')
      , @second = CONVERT(XML, @s).value('(/row)[2]', 'varchar(100)')
      , @third = CONVERT(XML, @s).value('(/row)[3]', 'varchar(100)')
SELECT  @first
      , @second
      , @third

This will work just fine as long as the strings does not contain any `&` or `<` characters. — Mikael Eriksson, Dec 15 '11 at 07:02

score 1 · Accepted Answer · answered Dec 14 '11 at 23:44

If the split function you're using returns the three values in a table, one option might be to use that output to insert into a table variable with an integer identity, and then pick which variable pairs with which identity value:

DECLARE @first VARCHAR(100)
DECLARE @second VARCHAR(100)
DECLARE @third VARCHAR(100) 

declare @split_output table (block varchar(100) )

declare @split_identity table (in_order int identity(1,1), block varchar(100))


/* recreate output of split fn */
insert into @split_output
select 'aaaa'
union
select 'bbbb'
union
select 'cccc'

select * from @split_output


/* push split output into table with identity column */
insert into @split_identity (block)
select block from @split_output

select * from @split_identity


/* Use identity value to pick which row of the table goes with which variable */
set @first = (select block from @split_identity where in_order = 1)
set @second = (select block from @split_identity where in_order = 2)
set @third  = (select block from @split_identity where in_order = 3)

select @first, @second, @third

score 0 · Answer 3 · answered Dec 15 '11 at 05:15

declare @s varchar(100)
set @s='aaaa,bbbb,cccc'

declare @first varchar(100)
declare @second varchar(100)
declare @third varchar(100)

select @first = left(@s, T.C1-1),
       @second = substring(@s, T.C1+1, T.C2-T.C1),
       @third = stuff(@s, 1, T.C2+1, '')
from (select charindex(',', @s),
             len(@s)-charindex(',', reverse(@s))) as T(C1, C2)

String Functions (Transact-SQL)

Split string into three variables in SQL Server 2005

3 Answers3