Click here to Skip to main content
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL-Server
I have collection of comma separated numbers.
For ex:-
Need to write script in SQL which will return result like 1,2,3,4,5 I mean select the distinct numbers from this number collection without inserting them into tables.

Any answer would be appreciated.
Posted 6-Dec-12 7:36am
Edited 6-Dec-12 7:40am
TRK3 at 6-Dec-12 14:36pm
You say "I have a collection of comma separated numbers" ... "without inserting them into tables" !
How is it possible in SQL to have a collection of anything in any format if it isn't already inserted into a a table ?
Either the data is already in a field in a table, or it isn't.
If it isn't already in the database and you don't want to put it in the database, then why do you want to manipulate it via SQL?
Use the right tool for the job.
Sk. Tajbir at 6-Dec-12 14:37pm
I think its possible to return distinct numbers, but can you give more information on your table.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

You could do this:
DECLARE @nums varchar(max)
DECLARE @comma char(1)
DECLARE @xml xml
SELECT @nums = '1,2,3,1,1,2,2,3,3,3,3,3,3,3,4,5,5,5'
SELECT @comma = ','
SELECT @xml = CONVERT(xml,'<r><n>' + REPLACE(@nums,@comma,'</n><n>') + '</n></r>')
SELECT DISTINCT [Value] = X.num.value('.','int')
FROM @xml.nodes('/r/n') X(num)
ORDER BY [Value]
anil.luck at 7-Dec-12 1:20am
@BC @ CV,
Good job thank you

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 370
1 Nirav Prabtani 313
2 _Amy 275
3 Richard Deeming 250
4 CHill60 185
0 _Amy 110
1 Maciej Los 90
2 OriginalGriff 85
3 Mehdi Gholam 80
4 Sergey Alexandrovich Kryukov 75

Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100