Hoe can we get - 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)

Hoe can we get

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2009-03-03 : 06:38:29

i will get a in parameter as comma sparated string
that i need to show out put as a set of rows
example
inparameter is 1,2,3,4,23,45,.....
i need to get like this
1
2
3
4
23
45
.
.
.
.

is the any function to get like this
thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 06:41:50

See function fnParseList
here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-03 : 06:50:40

[code]
declare @str1 varchar(max)
set @str1= '1,2,3,4,23,45'

SELECT SUBSTRING(@str1,charindex(',',@str1,v.number)+1,abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))-1)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) = ','
[/code]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 07:42:37

Declare @Str1 VARCHAR(100)
Select @Str1 = '1,2,3,4,56,45'
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) from csvtbl c

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-03 : 07:43:09

[code]
declare @str varchar(max)
set @str= '1,2,3,4,23,45'

;WITH Mangal AS (
SELECT

CAST('<i>' + REPLACE(@Str, ',', '</i><i>') + '</i>' AS XML) AS Id

)
SELECT
Split.a.value('.', 'VARCHAR(10)') AS Names
FROM Mangal
CROSS APPLY Id.nodes('//i') Split(a)

[/code]
http://mangalpardeshi.blogspot.com/2009/03/how-to-split-comma-delimited-string.html

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 07:45:07

small modification in ur query,

quote:

Originally posted by Mangal Pardeshi


declare @str varchar(max)
set @str= '1,2,3,4,23,45'


;WITH Mangal AS (
    SELECT 

        CAST('<i>' + REPLACE(@Str, ',', '</i><i>') + '</i>' AS XML) AS Id

)
SELECT 
    Split.a.value('.', 'VARCHAR(10)') AS Names
FROM Mangal
CROSS APPLY Id.nodes('//i') Split(a)

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-03-03 : 08:06:48

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-03 : 08:08:16

quote:
Originally posted by Mangal Pardeshi

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-03 : 09:33:42

quote:

Originally posted by bklr


declare @str1 varchar(max)
set @str1= '1,2,3,4,23,45'

SELECT	SUBSTRING(@str1,charindex(',',@str1,v.number)+1,abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))-1)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) = ','

this has limitation. if number of delimited values exceeds the number of available records in spt_values table, then this wont work.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-03 : 23:12:37

quote:
Originally posted by visakh16

quote:
Originally posted by bklr

declare @str1 varchar(max)
set @str1= '1,2,3,4,23,45'

SELECT SUBSTRING(@str1,charindex(',',@str1,v.number)+1,abs(charindex(',',@str1,charindex(',',@str1,v.number)+1)-charindex(',',@str1,v.number))-1)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) = ','

this has limitation. if number of delimited values exceeds the number of available records in spt_values table, then this wont work.

yes visakh,
i works upto 2047 delimited values only.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-03 : 23:32:08

This is a simple way to do it for strings that are reasonable size, like a few hundred items or less


declare @sql varchar(max)
set @sql = '1,2,3,4,23,45'
set @sql = replace(@sql,',',' union all select ')
execute ('select Number = '+@sql+' order by 1')


Number
-----------
1
2
3
4
23
45

(6 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources