Splitting out a fields value - 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 out a fields value

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-03-07 : 06:59:56

Hi
I have a field that has the following value 001, 002, 003, 004
I want to select the different value as
col col2 col3 col4
001 002 003 004

Sometimes there might only be one value, sometimes none sometimes two, three or four

Thanks

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-07 : 07:12:31

Declare @str varchar(50)
set @str='001, 002, 003, 004'
select parsename(replace(@str,',','.'),4)as col1,
parsename(replace(@str,',','.'),3)as col2,
parsename(replace(@str,',','.'),2)as col3,
parsename(replace(@str,',','.'),1)as col4

Note if the number of values if more than 4 then parsename wont work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-07 : 08:04:31

For any number of columns

Declare @str varchar(50),@sql varchar(8000)
set @str='001, 002, 003, 004, 005, 006'
set @sql='select '''+replace(@str,',',''',''')+''''
exec(@sql)

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-03-07 : 11:02:34

thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-03-07 : 13:44:48

The better answer is "Please refer to Rule 1 of 12" ...

Jay
to here knows when

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-07 : 23:10:02