Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
Hi,
I have collection of comma separated numbers.
For ex:-
1,2,3,1,1,2,2,3,3,3,3,3,3,3,4,555
 
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
v3
Comments
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
good
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]
  Permalink  
v3
Comments
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)



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