splitting string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

splitting string

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-16 : 08:57:43

i have a string
mystring='red,blue,yellow' (always 3)

I want to split this so
select @color1='red'
select @color2='blue'
select @color3='yellow'

how do i do this

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 09:26:22

[code]select f.Val FROM dbo.ParseValues(@mystring,',')f[/code]

ParseValues can be found below

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-16 : 09:33:28

If it is always 3

declare @s varchar(100)
set @s='red,blue,yellow'
select parsename(data,3),parsename(data,2),parsename(data,1) from
(
select replace(@s,',','.') as data
) as t

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-16 : 09:38:05

thanks -how could i then get these in separate variables

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-16 : 09:39:46

See my method. You can change it to assign to variables

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 09:42:14

[code]select @color1=MAX(CASE WHEN f.ID=1 THEN f.Val ELSE NULL END),
@color2=MAX(CASE WHEN f.ID=2 THEN f.Val ELSE NULL END),
@color3=MAX(CASE WHEN f.ID=3 THEN f.Val ELSE NULL END)
FROM dbo.ParseValues(@mystring,',')f
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-16 : 10:55:03

can you tell me the parsevalues function?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:15:08

what?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-16 : 11:50:39

sorry missed part of the thread - thanks that worked!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:53:32

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 02:31:34

quote:
Originally posted by esthera

sorry missed part of the thread - thanks that worked!

Have you seen my reply?
For three parts you dont need a seperate function

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-03-17 : 02:46:00

yes thanks - is any faster or slower

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 03:23:28

quote:
Originally posted by esthera

yes thanks - is any faster or slower

Why dont you test and let us know the result?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 03:45:54

quote:
Originally posted by madhivanan

quote:
Originally posted by esthera

sorry missed part of the thread - thanks that worked!

Have you seen my reply?
For three parts you dont need a seperate function

Madhivanan

Failing to plan is Planning to fail

will cause problems if input is not consistent ie. always not containing 3 parts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 04:51:35

Yes. But OP specified it has always 3 parts

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources