convert comma separated string values into integer values RRS feed

  • Question

  • Hi, I have a string variable
    string str1="1,2,3,4,5";

    I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server.
    my query is 

    declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))

    -- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)

    But this query throws following error message:

    Conversion failed when converting the varchar value '3,4,6,7' to data type int.

    kindly help me out.

    Mayank Mani Pandey India

    Tuesday, July 28, 2015 1:20 PM


All replies

  • this code project post refers to same excat problem and also provides you the solution

    refer this 


    Hope it Helps!!

    Tuesday, July 28, 2015 1:27 PM
  • The best choice is to use a table-valued parameter and skip comma-separated lists altogether.

    Else, this article on my web site explains why the IN thing does not work and presents (too) many methods to crack the list into a table.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eric__Zhang Wednesday, August 5, 2015 2:17 AM
    Tuesday, July 28, 2015 1:35 PM
  • Try this:

    declare @id varchar(50)
    set @id= '3,4,6,7,2'
    --set @id=(select replace(@id,'''',''))
    -- in below select query Id is of Integer datatype
    select *from ehsservice  where   charindex( cast(id as nvarchar(2))+',',@id,1) > 0

    Please click "Mark As Answer" if my post helped.

    • Edited by Berimi Tuesday, July 28, 2015 1:39 PM
    Tuesday, July 28, 2015 1:35 PM
  • It's not working @Berimi
    Thursday, July 7, 2016 9:05 AM
  • DECLARE @xml AS       XML,
           @str AS       VARCHAR(100),
           @delimiter AS VARCHAR(10);
    SET @str = '12345,87612,988473';
    SET @delimiter = ',';
    SET @xml = CAST('<X>' + REPLACE(@str, @delimiter, '</X><X>') + '</X>' AS XML); 
    SELECT [N].value( '.', 'varchar(50)' ) AS value
    FROM @xml.nodes( 'X' ) AS [T]( [N] );
    This worked for me.

    For more info check- this article
    Thursday, July 7, 2016 9:08 AM
  • SQL Server 2016 has introduced a new feature which is much simpler to split strings, I've discussed it here: https://sqlwithmanoj.com/2016/03/10/new-in-built-table-valued-function-string_split-in-sql-server-2016-to-split-strings/

    declare @id varchar(50)
    set @id= '3,4,6,7'
    select * from STRING_SPLIT('3,4,6,7', ',')

    But if you are below than SQL 2016, then plz check solution provided by Erland and Shailesh.

    ~manoj | SQLwithManoj.com

    Thursday, July 7, 2016 9:35 AM
  • Thanks, working fine

    • Edited by Rupom Thursday, April 4, 2019 3:40 AM
    Thursday, April 4, 2019 3:39 AM