How can I select from list of values in SQL Server

Question

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

User · Accepted Answer

Simplest way to get the distinct values of a long list of comma delimited text would be to use a find an replace with 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   Applied to your long line of comma delimited text   Find and replace every comma with UNION SELECT Add a SELECT in front of the statement   You now should have a working query

User · Answer

Another way that you can use is a query like this   SELECT DISTINCT     LTRIM m n value    1    varchar 8000     as columnName FROM       SELECT CAST   lt XMLRoot gt  lt RowData gt     REPLACE t val       lt  RowData gt  lt RowData gt        lt  RowData gt  lt  XMLRoot gt   AS XML  AS x      FROM  SELECT  1  1  1  2  5  1  6   AS t val        dt   CROSS APPLY      x nodes   XMLRoot RowData   m n

User · Answer

Available only on SQL Server 2008 and over is row-constructor in this form  You could use   SELECT DISTINCT   FROM  VALUES  1    1    1    2    5    1    6   AS X a    Many wrote about  among them     MS official  https   docs microsoft com en-us sql t-sql queries table-value-constructor-transact-sql http   www sql-server-helper com sql-server-2008 row-value-constructor-as-derived-table aspx

User · Answer

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

User · Answer

A technique that has worked for me is to query a table that you know has a large amount of records in it  including just the Row Number field in your result  Select Top 10000 Row Number   OVER  Order by fieldintable  As  recnum  From largetable   will return a result set of 10000 records from 1 to 10000  use this within another query to give you the desired results

User · Answer

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  gt  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 · Answer

PostgreSQL  gives you 2 ways of doing this   SELECT DISTINCT   FROM  VALUES  a     b     a     v    AS tbl col1    or  SELECT DISTINCT   FROM  select unnest array  a   b    a   v     AS tbl col1    using array approach you can also do something like this   SELECT DISTINCT   FROM  select unnest string to array  a b c d e f a b d          AS tbl col1

User · Answer

In general    SELECT    DISTINCT        FieldName1  FieldName2       FieldNameN FROM         Values           ValueForField1  ValueForField2      ValueForFieldN              ValueForField1  ValueForField2      ValueForFieldN              ValueForField1  ValueForField2      ValueForFieldN              ValueForField1  ValueForField2      ValueForFieldN              ValueForField1  ValueForField2      ValueForFieldN       AS TempTableName   FieldName1  FieldName2       FieldNameN     In your case     Select    distinct   TempTableName Field1  From        VALUES      1         1         1         2         5         1         6      AS TempTableName  Field1

User · Answer

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

User · Answer

If  you need an array  separate the array columns with a comma   SELECT   FROM  VALUES  WOMENS     MENS     CHILDRENS    as X  Attribute     VALUES 742   318   AS z  StoreID

User · Answer

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 · Answer

Have you tried using the following syntax   select   from  values  1    2    3    4    5   numbers number

User · Answer

Select user id from list of user id       SELECT   FROM my table WHERE user id IN  1 3 5 7 9 4

User · Answer

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  gt  1   produces  2 5 6

User · Answer

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

[sql-server] How can I select from list of values in SQL Server

Examples related to sql-server

Examples related to select-query