Click here to Skip to main content
12,998,054 members (60,002 online)
Rate this:
Please Sign up or sign in to vote.
See more:
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
Updated 6-Dec-12 7:40am
TRK3 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 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 7-Dec-12 1:20am
@BC @ CV,
Good job thank you
Rate this: bad
Please Sign up or sign in to vote.

Solution 1


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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170622.1 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2017
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