How can I select from list of values in SQL Server

user_147078 0
Viewed 561332

I have very simple problem that I can't solve. I need to do something like this:

select distinct * from (1, 1, 1, 2, 5, 1, 6).

Anybody can help??

Edit

The data comes as a text file from one of our clients. It's totally unformatted (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 these values in my sql query. It's not convenient to do so every time I need to run a query.

select-query sql-server
16 Answers

I know this is a pretty old thread, but I was searching for something similar and came up with this.

Given that you had a comma-separated string, you could use string_split

select distinct value from string_split('1, 1, 1, 2, 5, 1, 6',',')

This should return

1
2
5
6

String split takes two parameters, the string input, and the separator character.

you can add an optional where statement using value as the column name

select distinct value from string_split('1, 1, 1, 2, 5, 1, 6',',')
where value > 1

produces

2
5
6
user_11712030

Using GROUP BY gives you better performance than DISTINCT:

SELECT *
FROM
(
    VALUES
        (1),
        (1),
        (1),
        (2),
        (5),
        (1),
        (6)
) AS A (nums)
GROUP BY A.nums;
user_143484560

If it is a list of parameters from existing SQL table, for example ID list from existing Table1, then you can try this:

select distinct ID
      FROM Table1
      where 
      ID in (1, 1, 1, 2, 5, 1, 6)
ORDER BY ID;

Or, if you need List of parameters as a SQL Table constant(variable), try this:

WITH Id_list AS (
     select ID
      FROM Table1
      where 
      ID in (1, 1, 1, 2, 5, 1, 6)
)
SELECT distinct * FROM Id_list
ORDER BY ID;
user_65316290

I create a function on most SQL DB I work on to do just this.

CREATE OR ALTER FUNCTION [dbo].[UTIL_SplitList](@parList Varchar(MAX),@splitChar Varchar(1)=',') 
  Returns @t table (Column_Value varchar(MAX))
  as
  Begin
    Declare @pos integer 
    set @pos = CharIndex(@splitChar, @parList)
    while @pos > 0
    Begin
      Insert Into @t (Column_Value) VALUES (Left(@parList, @pos-1))
      set @parList = Right(@parList, Len(@parList) - @pos)
      set @pos = CharIndex(@splitChar, @parList)
    End
    Insert Into @t (Column_Value) VALUES (@parList)
    Return
  End

Once the function exists, it is as easy as

SELECT DISTINCT 
    *
FROM 
    [dbo].[UTIL_SplitList]('1,1,1,2,5,1,6',',')
user_121491380

Select user id from list of user id: 

SELECT * FROM my_table WHERE user_id IN (1,3,5,7,9,4);
user_103272230

Use the SQL In function

Something like this:

SELECT * FROM mytable WHERE:
"VALUE" In (1,2,3,7,90,500)

Works a treat in ArcGIS

user_30593840
Related