Passing a varchar full of comma delimited values to a SQL Server IN function

Question

Duplicate of   Dynamic SQL Comma Delimited Value Query   Parameterized Queries with Like and In    I have a SQL Server Stored Procedure where I would like to pass a varchar full of comma delimited values to an IN function  For example   DECLARE  Ids varchar 50   SET  Ids    1 2 3 5 4 6 7 98 234    SELECT    FROM sometable  WHERE tableid IN   Ids     This does not work of course  I get the error      Conversion failed when converting the varchar value  1 2 3 5 4 6 7 98 234  to data type int    How can I accomplish this  or something relatively similar  without resorting to building dynamic SQL

User · Answer

Create a table function like below which parse comma separated varchar and returns a table that can be inner joined with other tables.

CREATE FUNCTION [dbo].[fn_SplitList]
(
  @inString     varchar(MAX)  = '',
  @inDelimiter  char(1)       = ',' -- Keep the delimiter to 100 chars or less.  Generally a delimiter will be 1-2 chars only.
)
RETURNS @tbl_Return  table
(
  Unit  varchar(1000) COLLATE Latin1_General_BIN
)
AS
BEGIN 
    INSERT INTO @tbl_Return
    SELECT DISTINCT
      LTRIM(RTRIM(piece.value('./text()[1]', 'varchar(1000)'))) COLLATE DATABASE_DEFAULT AS Unit
    FROM
    (
      --
      --  Replace any delimiters in the string with the "X" tag.
      --
      SELECT
        CAST(('<X>' + REPLACE(s0.prsString, s0.prsSplitDelimit, '</X><X>') + '</X>') AS xml).query('.') AS units
      FROM
      (
        --
        --  Convert the string and delimiter into XML.
        --
        SELECT
          (SELECT @inString FOR XML PATH('')) AS prsString,
          (SELECT @inDelimiter FOR XML PATH('')) AS prsSplitDelimit
      ) AS s0
    ) AS s1
    CROSS APPLY units.nodes('X') x(piece)
  RETURN
END

================================================= Now consume above created table function in your code,creation of function is one time activity in your database that can be used across databases as well on same server.

DECLARE @Ids varchar(50);
SET @Ids = '1,2,3,5,4,6,7,98,234';

SELECT
     *
FROM sometable AS st
INNER JOIN fn_SplitList(@ids, ',') AS sl
     ON sl.unit = st.tableid

User · Answer

If you use SQL Server 2008 or higher  use table valued parameters  for example   CREATE PROCEDURE  dbo   GetAccounts   accountIds nvarchar  AS BEGIN     SELECT        FROM accountsTable      WHERE accountId IN  select   from  accountIds  END  CREATE TYPE intListTableType AS TABLE  n int NOT NULL   DECLARE  tvp intListTableType   -- inserts each id to one row in the tvp table     INSERT  tvp n  VALUES  16509   16685   46173   42925   46167   5511   EXEC GetAccounts  tvp

User · Answer

No Table No Function No Loop  Building on the idea of parsing your list into a table our DBA suggested using XML   Declare  Ids varchar 50  Set  Ids      1 2 3 5 4 6 7 98 234     DECLARE  XML XML SET  XML   CAST   lt i gt     REPLACE  Ids         lt  i gt  lt i gt        lt  i gt   AS XML   SELECT    FROM     SomeTable      INNER JOIN  XML nodes  i   x i           ON  SomeTable  Id   x i value       VARCHAR MAX      These seems to have the same performance as  KM s answer but  I think  a lot simpler

User · Answer

Its been a while but I have done this in the past using XML as a interim   I can t take any credit for this  but I m afraid I no longer know where I got this idea from   -- declare the variables needed DECLARE  xml as xml  str as varchar 100   delimiter as varchar 10   -- The string you want to split SET  str  A B C D E Bert Ernie 1 2 3 4 5   -- What you want to split on  Can be a single character or a string SET  delimiter       -- Convert it to an XML document SET  xml   cast    lt X gt   replace  str  delimiter    lt  X gt  lt X gt      lt  X gt    as xml   -- Select back from the XML SELECT N value       varchar 10    as value FROM  xml nodes  X   as T N

User · Answer

Best and simple approach   DECLARE  AccumulateKeywordCopy NVARCHAR 2000   IDDupCopy NVARCHAR 50   SET  AccumulateKeywordCopy      SET  IDDupCopy      SET  IDDup    SELECT CONVERT VARCHAR MAX    lt columnName gt   FROM  lt tableName gt  WHERE  lt clause gt    SET  AccumulateKeywordCopy        AccumulateKeyword      SET  IDDupCopy        IDDup       SET  IDDupCheck   CHARINDEX  IDDupCopy  AccumulateKeywordCopy

User · Answer

I ran into the same issue  and I don t want to have any footprint on the source database - i e  no stored procedures or functions  I went about it this way  declare  IDs table  Value int   insert into  IDs values 1  insert into  IDs values 2  insert into  IDs values 3  insert into  IDs values 5  insert into  IDs values 4  insert into  IDs values 6  insert into  IDs values 7  insert into  IDs values 98  insert into  IDs values 234    SELECT    FROM sometable  WHERE tableid IN  select Value from  IDs

User · Answer

You could do it like this   create or replace  PROCEDURE UDP SETBOOKMARK      P USERID IN VARCHAR2     P BOOKMARK IN VARCHAR2     AS  BEGIN  UPDATE T ER Bewertung SET LESEZEICHEN   P BOOKMARK WHERE STAMM ID in  select regexp substr P USERID          1  level  from dual                    connect by regexp substr P USERID           1  level  is not null   and ER ID    select max ER ID  from T ER Bewertung Kopie    commit   END UDP SETBOOKMARK    Then try it with  Begin UDP SETBOOKMARK   1 2 3 4 5    Test    End    You can use this IN-Clause with regexp substr in other situations too  just try it

User · Answer

It s a very common question  Canned answer  several nice techniques   http   www sommarskog se arrays-in-sql-2005 html

User · Answer

I can suggest using WITH like this   DECLARE  Delim char 1         SET  Ids    Ids    Delim   WITH CTE i  ls  id  AS       SELECT 1  CHARINDEX  Delim   Ids  1   SUBSTRING  Ids  1  CHARINDEX  Delim   Ids  1  - 1      UNION ALL     SELECT i   1  CHARINDEX  Delim   Ids  ls   1   SUBSTRING  Ids  ls   1  CHARINDEX  Delim   Ids  ls   1  - CHARINDEX  Delim   Ids  ls  - 1      FROM CTE     WHERE  CHARINDEX  Delim   Ids  ls   1   gt  1   SELECT t   FROM yourTable t     INNER JOIN     CTE c     ON t id   c id

User · Answer

CREATE TABLE t            id   INT        col1 VARCHAR 50         INSERT INTO t  VALUES      1                param1     INSERT INTO t  VALUES      2                param2     INSERT INTO t  VALUES      3                param3     INSERT INTO t  VALUES      4                param4     INSERT INTO t  VALUES      5                param5     DECLARE  params VARCHAR 100    SET  params     param1 param2 param3     SELECT    FROM   t  WHERE  Charindex       Cast col1 AS VARCHAR 8000           params   gt  0    working fiddle find here Fiddle

User · Answer

Thanks  for your function I Used IT                         This is my EXAMPLE    UPDATE  RD   PurchaseOrderHeader  SET      DispatchCycleNumber    10   WHERE  OrderNumber in select   FROM XA fn SplitOrderIDs  InvoiceNumberList       CREATE FUNCTION  XA   fn SplitOrderIDs         OrderList varchar 500    RETURNS   ParsedList table       OrderID int   AS BEGIN     DECLARE  OrderID varchar 10    Pos int      SET  OrderList   LTRIM RTRIM  OrderList            SET  Pos   CHARINDEX       OrderList  1       IF REPLACE  OrderList            lt  gt         BEGIN         WHILE  Pos  gt  0         BEGIN                 SET  OrderID   LTRIM RTRIM LEFT  OrderList   Pos - 1                    IF  OrderID  lt  gt                     BEGIN                         INSERT INTO  ParsedList  OrderID                           VALUES  CAST  OrderID AS int   --Use Appropriate conversion                 END                 SET  OrderList   RIGHT  OrderList  LEN  OrderList  -  Pos                  SET  Pos   CHARINDEX       OrderList  1           END     END      RETURN END

User · Answer

Don t use a function that loops to split a string   my function below will split a string very fast  with no looping   Before you use my function  you need to set up a  helper  table  you only need to do this one time per database   CREATE TABLE Numbers  Number int  NOT NULL      CONSTRAINT PK Numbers PRIMARY KEY CLUSTERED  Number ASC WITH  PAD INDEX    OFF  STATISTICS NORECOMPUTE    OFF  IGNORE DUP KEY   OFF  ALLOW ROW LOCKS    ON  ALLOW PAGE LOCKS    ON  ON  PRIMARY    ON  PRIMARY  DECLARE  x int SET  x 0 WHILE  x lt 8000 BEGIN     SET  x  x 1     INSERT INTO Numbers VALUES   x  END   use this function to split your string  which does not loop and is very fast   CREATE FUNCTION  dbo   FN ListToTable          SplitOn              char 1               --REQUIRED  the character to split the  List string on       List                 varchar 8000         --REQUIRED  the list to split apart   RETURNS  ParsedList table       ListValue varchar 500    AS BEGIN      Takes the given  List string and splits it apart based on the given  SplitOn character  A table is returned  one row per split item  with a column name  ListValue   This function workes for fixed or variable lenght items  Empty and null items will not be included in the results set    Returns a table  one row per item in the list  with a column name  ListValue   EXAMPLE  ---------- SELECT   FROM dbo FN ListToTable      1 12 123 1234 54321 6 A          B        returns          ListValue           -----------         1         12         123         1234         54321         6         A                               B           10 row s  affected          ---------------- --SINGLE QUERY-- --this will not return empty rows ---------------- INSERT INTO  ParsedList          ListValue      SELECT         ListValue         FROM  SELECT                   LTRIM RTRIM SUBSTRING List2  number 1  CHARINDEX  SplitOn  List2  number 1 -number - 1    AS ListValue                   FROM                              SELECT  SplitOn    List    SplitOn AS List2                          AS dt                       INNER JOIN Numbers n ON n Number  lt  LEN dt List2                    WHERE SUBSTRING List2  number  1     SplitOn                dt2         WHERE ListValue IS NOT NULL AND ListValue        RETURN  END --Function FN ListToTable   you can use this function as a table in a join   SELECT     Col1  COl2  Col3        FROM  YourTable         INNER JOIN FN ListToTable      YourString  s ON  YourTable ID   s ListValue   Here is your example   Select   from sometable where tableid in SELECT ListValue FROM dbo FN ListToTable      Ids  s

User · Answer

You can create a function that returns a table    so your statement would be something like   select   from someable   join Splitfunction  ids  as splits on sometable id   splits id   Here is a simular function    CREATE FUNCTION  dbo   FUNC SplitOrderIDs         OrderList varchar 500    RETURNS   ParsedList table       OrderID int   AS BEGIN     DECLARE  OrderID varchar 10    Pos int      SET  OrderList   LTRIM RTRIM  OrderList            SET  Pos   CHARINDEX       OrderList  1       IF REPLACE  OrderList            lt  gt         BEGIN         WHILE  Pos  gt  0         BEGIN             SET  OrderID   LTRIM RTRIM LEFT  OrderList   Pos - 1                IF  OrderID  lt  gt                 BEGIN                 INSERT INTO  ParsedList  OrderID                   VALUES  CAST  OrderID AS int   --Use Appropriate conversion             END             SET  OrderList   RIGHT  OrderList  LEN  OrderList  -  Pos              SET  Pos   CHARINDEX       OrderList  1           END     END      RETURN END

User · Answer

Try this   SELECT ProductId  Name  Tags   FROM Product   WHERE  1 2 3   LIKE       CAST ProductId AS VARCHAR 20              As said on the last example of this link

User · Answer

This came in handy for one of my requirements where I did not want to use CTE and also did not want to go with the inner join   DECLARE  Ids varchar 50   SET  Ids    1 2 3 5 4 6 7 98 234    SELECT    cn1 cn2 cn3 FROM tableName WHERE columnName in  select Value from fn SplitList  ids

User · Answer

This works perfectly  The below answers are too complicated  Don t look at this as dynamic  Set up your store procedure as follows     id as varchar 50   as  Declare  query as nvarchar max  set  query    select   from table where id in    id     EXECUTE sp executesql  query

User · Answer

Of course if you re lazy like me  you could just do this   Declare  Ids varchar 50  Set  Ids     1 2 3 5 4 6 7 98 234    Select   from sometable  where Charindex     cast tableid as varchar 8000         Ids   gt  0

User · Answer

I ve written a stored procedure to show how to do this before  You basically have to process the string  I tried to post the code here but the formatting got all screwy   IF EXISTS  SELECT   FROM dbo sysobjects WHERE id   object id N  dbo   uspSplitTextList    AND OBJECTPROPERTY id  N IsProcedure     1     DROP PROCEDURE  dbo   uspSplitTextList  GO  SET QUOTED IDENTIFIER ON  GO SET ANSI NULLS ON  GO                                                                                   -- uspSplitTextList -- -- Description  --    splits a separated list of text items and returns the text items -- -- Arguments  --     list text        - list of text items --     Delimiter        - delimiter -- -- Notes  -- 02 22 2006 - WSR   use DATALENGTH instead of LEN throughout because LEN doesn t count trailing blanks -- -- History  -- 02 22 2006 - WSR   revised algorithm to account for items crossing 8000 character boundary -- 09 18 2006 - WSR   added to this project -- CREATE PROCEDURE uspSplitTextList     list text           text      Delimiter           varchar 3  AS  SET NOCOUNT ON  DECLARE  InputLen       integer         -- input text length DECLARE  TextPos        integer         -- current position within input text DECLARE  Chunk          varchar 8000    -- chunk within input text DECLARE  ChunkPos       integer         -- current position within chunk DECLARE  DelimPos       integer         -- position of delimiter DECLARE  ChunkLen       integer         -- chunk length DECLARE  DelimLen       integer         -- delimiter length DECLARE  ItemBegPos     integer         -- item starting position in text DECLARE  ItemOrder      integer         -- item order in list DECLARE  DelimChar      varchar 1       -- first character of delimiter  simple delimiter   -- create table to hold list items -- actually their positions because we may want to scrub this list eliminating bad entries before substring is applied CREATE TABLE  list items   item order integer  item begpos integer  item endpos integer    -- process list IF  list text IS NOT NULL    BEGIN     -- initialize    SET  InputLen   DATALENGTH  list text     SET  TextPos   1    SET  DelimChar   SUBSTRING  Delimiter  1  1     SET  DelimLen   DATALENGTH  Delimiter     SET  ItemBegPos   1    SET  ItemOrder   1    SET  ChunkLen   1     -- cycle through input processing chunks    WHILE  TextPos  lt    InputLen AND  ChunkLen  lt  gt  0       BEGIN        -- get current chunk       SET  Chunk   SUBSTRING  list text   TextPos  8000         -- setup initial variable values       SET  ChunkPos   1       SET  ChunkLen   DATALENGTH  Chunk        SET  DelimPos   CHARINDEX  DelimChar   Chunk   ChunkPos         -- loop over the chunk  until the last delimiter       WHILE  ChunkPos  lt    ChunkLen AND  DelimPos  lt  gt  0          BEGIN           -- see if this is a full delimiter          IF SUBSTRING  list text    TextPos    DelimPos - 1    DelimLen     Delimiter             BEGIN              -- insert position             INSERT INTO  list items  item order  item begpos  item endpos              VALUES   ItemOrder   ItemBegPos    TextPos    DelimPos - 1  - 1               -- adjust positions             SET  ItemOrder    ItemOrder   1             SET  ItemBegPos     TextPos    DelimPos - 1     DelimLen             SET  ChunkPos    DelimPos    DelimLen              END          ELSE             BEGIN              -- adjust positions             SET  ChunkPos    DelimPos   1              END           -- find next delimiter                SET  DelimPos   CHARINDEX  DelimChar   Chunk   ChunkPos            END        -- adjust positions       SET  TextPos    TextPos    ChunkLen        END     -- handle last item    IF  ItemBegPos  lt    InputLen       BEGIN        -- insert position       INSERT INTO  list items  item order  item begpos  item endpos        VALUES   ItemOrder   ItemBegPos   InputLen         END     -- delete the bad items    DELETE FROM  list items    WHERE item endpos  lt  item begpos     -- return list items    SELECT SUBSTRING  list text  item begpos   item endpos - item begpos   1   AS item text  item order  item begpos  item endpos    FROM  list items    ORDER BY item order     END  DROP TABLE  list items  RETURN                                                                                   GO SET QUOTED IDENTIFIER OFF  GO SET ANSI NULLS ON  GO

User · Answer

-- select   from dbo Split ID  77 106          ALTER FUNCTION dbo Split ID  String varchar 8000            returns  temptable TABLE  ID varchar 8000            as          begin              declare  idx int              declare  slice varchar 8000               declare  Delimiter char 1           set  Delimiter               select  idx   1                  if len  String  lt 1 or  String is null  return               while  idx   0              begin                  set  idx   charindex  Delimiter  String                   if  idx  0                      set  slice   left  String  idx - 1                   else                      set  slice    String                   if len  slice  gt 0                  insert into  temptable ID  values  slice                    set  String   right  String len  String  -  idx                   if len  String    0 break              end      return          end

User · Answer

I have same idea with user KM  but do not need extra table Number  Just this function only   CREATE FUNCTION  dbo   FN ListToTable         SplitOn              char 1               --REQUIRED  the character to split the  List string on      List                 varchar 8000         --REQUIRED  the list to split apart   RETURNS  ParsedList table       ListValue varchar 500    AS BEGIN     DECLARE  number int   0     DECLARE  childString varchar 502           DECLARE  lengthChildString int   0     DECLARE  processString varchar 502     SplitOn    List    SplitOn      WHILE  number  lt  LEN  processString      BEGIN         SET  number    number   1         SET  lengthChildString   CHARINDEX  SplitOn   processString   number   1  -  number - 1         IF  lengthChildString  gt  0         BEGIN             SET  childString   LTRIM RTRIM SUBSTRING  processString   number   1   lengthChildString                 IF  childString IS NOT NULL AND  childString                   BEGIN                 INSERT INTO  ParsedList ListValue  VALUES   childString                  SET  number    number    lengthChildString - 1             END         END     END  RETURN  END   And here is the test   SELECT ListValue FROM dbo FN ListToTable      a     bb c     Result      ListValue                           a    bb    c

User · Answer

The simplest way i found was to use FIND IN SET  FIND IN SET column name  values   values  1 2 3   SELECT name WHERE FIND IN SET id  values

User · Answer

I think a very simple solution could be following   DECLARE  Ids varchar 50   SET  Ids    1 2 3 5 4 6 7 98 234    SELECT    FROM sometable  WHERE      Ids     LIKE      CONVERT VARCHAR 50  tableid

User · Answer

Error 493  The column  i  that was returned from the nodes   method cannot be     used directly  It can only be used with one of the four XML data type     methods  exist    nodes    query    and value    or in IS NULL and IS NOT     NULL checks    The above errorr was fixed in SQL Server 2014 by using following snippet  Declare  Ids varchar 50  Set  Ids    1 2 3 5 4 6 7 98 234   DECLARE  XML XML SET  XML   CAST   lt i gt     REPLACE  Ids         lt  i gt  lt i gt        lt  i gt   AS XML   SELECT SomeTable    FROM     SomeTable      cross apply  XML nodes  i   x i           where SomeTable  Id   x i value       VARCHAR MAX

User · Answer

Without using dynamic SQL  you have to take the input variable and use a split function to put the data into a temp table and then join to that

[sql] Passing a varchar full of comma delimited values to a SQL Server IN function

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-in