Click here to Skip to main content
11,486,023 members (77,205 online)
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 8:36am
Edited 6-Dec-12 8: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)

  Print Answers RSS
0 C#developer.asp 429
1 Sergey Alexandrovich Kryukov 375
2 Maciej Los 365
3 Mehdi Gholam 250
4 OriginalGriff 144
0 Sergey Alexandrovich Kryukov 8,819
1 OriginalGriff 8,047
2 Sascha Lefèvre 3,199
3 Maciej Los 2,886
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 6 Dec 2012
Copyright © CodeProject, 1999-2015
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