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

stackoverflow.com/questions/2647/split-string-in-sql may be helpful — devdigital
– devdigital, Commented Dec 14, 2011 at 19:58

Stuart Ainsworth · Accepted Answer · 2011-12-14 20:43:51Z

3

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

answered Dec 14, 2011 at 20:43

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

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

1 Comment

Mikael Eriksson Over a year ago

This will work just fine as long as the strings does not contain any & or < characters.

mikurski · Accepted Answer · 2011-12-14 23:44:57Z

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

Mikael Eriksson · Accepted Answer · 2011-12-15 05:15:42Z

0

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)

answered Dec 15, 2011 at 5:15

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Collectives™ on Stack Overflow

Split string into three variables in SQL Server 2005

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related