Click here to Skip to main content
12,446,146 members (84,868 online)
Rate this:
 
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
Updated 6-Dec-12 7:40am
v3
Comments
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
 
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 7-Dec-12 1:20am
   
@BC @ CV,
Good job thank you
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  

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.160811.3 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2016
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