row eliminator - 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

General SQL Server Forums

New to SQL Server Programming

row eliminator

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2009-02-24 : 01:44:29

Hi friends,

I have a value (1,2,3,4,5) i want result like this

test
----
1
2
3
4
5

Any Suggestion without using loop.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 01:47:31

[code]
try this
declare @str1 varchar(max)
set @str1= '1,2,3,4,5'

SELECT
replace(SUBSTRING(@str1,charindex(',',@str1,v.number),abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))),',','')as value
FROM master..spt_values AS v
WHERE v.Type = 'P'
AND v.number > 0
AND v.number <= len(@str1)
AND substring(',' + @str1, v.number, 1) = ','
ORDER BY VALUE
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 01:49:10

Try this once,



Declare @Str1 VARCHAR(100) 
Select @Str1 = '1,2,3,4,5'
Select @Str1 = @Str1 + ','

;WITH csvtbl(i, j)
AS
(
	SELECT 0, j = CHARINDEX(',', @Str1)
	UNION ALL
	SELECT CAST(j + 1 AS INT), j = CHARINDEX(',', @Str1, j + 1) 
	FROM csvtbl
	WHERE CHARINDEX(',', @Str1, j + 1) <> 0
)

SELECT SUBSTRING(@Str1, C.i, C.j-i) as test
from csvtbl C