ansaurus

Question

How can I select from list of values in SQL Server

Answer 1

+3 A:

Simplest way I know of would be to use a UNION to get the distinct values.

select 1
union select 1
union select 1
union select 2
union select 5
union select 1
union select 6

Lieven 2009-10-14 08:24:07

no, no, I have a list of several hundreds of values, manually it would be torture

Eedoh 2009-10-14 08:29:19

where does that list come from? It might be way easier to just copy /paste that list in Excel and extract the distinct values there using a simple crosstab.

Lieven 2009-10-14 08:30:52

btw, find and replace might also take you a long way. Replace every comma with *union select*, add a select in front and you should have a working query cfr the union I showed.

Lieven 2009-10-14 08:35:03

it comes as a text file from one of our clients. It's totally unformated (it's a single, very long line of text), but it may be possible to do so in Excel. But it's not practical for me, because I will need to use theese values in my sql query. It's not convenient to do so every time I need to run a query

Eedoh 2009-10-14 08:35:27

this stuff with replacing commas with select union works like a charm Thanks a lot :)

Eedoh 2009-10-14 08:41:34

no problem. [<sigh> Comments must be at least 15 characters in length.]

Lieven 2009-10-14 08:44:07

Answer 2

+1 A:

If you want to select only certain values from a single table you can try this

select distinct(*) from table_name where table_field in (1,1,2,3,4,5)

eg: select first_name,phone_number from telephone_list where district id in (1,2,5,7,8,9)
if you want to select from multiple tables then you must go for UNION.
If you just want to select the values 1, 1, 1, 2, 5, 1, 6 then you must do this

select 1 union select 1 union select 1 union select 2 union select 5 union select 1 union select 6

Anirudh Goel 2009-10-14 08:26:02

I don't need to select from a table, but from this list of values (in brackets). That's the main problem (selecting from comma separated array of values, not from a table)

Eedoh 2009-10-14 08:31:01

that case, like we have DUAL table in Oracle, you can make use of the same. But since there is no DUAL then you will have to go the union way. You can try another method, as you mentioned you have comma separated array of values, why don't you insert them to a table and then use a neat sql select query, instead of using so many sql unions.

Anirudh Goel 2009-10-14 08:35:54

Answer 3

+2 A:

This works on SQL Server 2005 and if there is maximal number:

SELECT * 
FROM
  (SELECT ROW_NUMBER() OVER(ORDER BY a.id) NUMBER
  FROM syscomments a
  CROSS JOIN syscomments b) c
WHERE c.NUMBER IN (1,4,6,7,9)

LukLed 2009-10-14 08:36:01

+1 neat but it is limited to the amount of rows in syscomments cross joined with itself. In my case to 294849. (and you forgot distinct.)

Lieven 2009-10-14 08:40:08

You can cross join once more, but replacing commas is much faster solution.

LukLed 2009-10-14 09:08:08

Yeah, this way is good also, but I prefer Lieven's solution, because of simplicity.

Eedoh 2009-10-14 10:41:29

Answer 4

A:

have a look at

http://www.williamrobertson.net/documents/comma-separated.html

Pragnesh Patel 2009-10-14 08:43:19

ansaurus

tags:

views:

answers:

How can I select from list of values in SQL Server

related questions