t-sql Convert comma separated string into int, without using user created function

Question

I'm passing a list of int's (comma separated)

ie. 1, 2, 3, 4

to my sp. But I'm getting an error because the list is a string, and I'm comparing to an int field. Is there a way for me to convert the list to int, without using a user created function?

Note: employeeID is INT

declare @intArray varchar(200)

SELECT *
FROM tbl_Employee
WHERE employeeID IN ( @intArray )

The error is "Cannot convert type varchar to int"

What version of SQL Server? And are you able to change the code that's calling your sp? — Damien_The_Unbeliever, Dec 17 '10 at 14:44
@Damien SQL Server 2005. Yes I'm able to change code calling my sp. — Since_2008, Dec 17 '10 at 15:13
rats. For 2008, I'd suggest changing to using table valued parameters. — Damien_The_Unbeliever, Dec 17 '10 at 15:21

score 19 · Answer 1 · edited Oct 10 '18 at 06:47

19

If you are using sql server 2016 and above then use STRING_SPLIT

declare @intArray varchar(200)
Set @intArray = '3,4,6,7'

SELECT *
FROM tbl_Employee
WHERE employeeID IN (select * from STRING_SPLIT(@intArray, ','))

edited Oct 10 '18 at 06:47

Alien

15,141
6
37
57

answered Oct 10 '18 at 06:44

Manish Kumar

201
2
2

2

Not only your SQL Server should be 2016, also the `compatibility_level` should be 130 or upper. Read more [Here](https://stackoverflow.com/a/47206082/4344976). – Muhammad Musavi May 21 '19 at 08:01
this one is working. its also a sure short solution. working in my case – Heemanshu Bhalla Mar 10 '20 at 06:19

score 14 · Accepted Answer · answered Dec 17 '10 at 14:40

14

You don't want to cast that list into an int, but into a list of ints.

There is no cast operator or function for that, but you can use dynamic SQL to get around it.

Basically you write

EXECUTE('SELECT * FROM tbl_Employee WHERE employeeID IN ('+@intArray+')')

Be aware of SQL injection attacks though!

answered Dec 17 '10 at 14:40

TToni

9,145
1
28
42

3

This is a good native way of doing what the OP wants. Keep in mind that as the list of employees and `ID`s grows, the `IN` phrase become increasingly ineffective. In 2005/2008, `EXISTS` is preferred. Also, dynamic SQL can't be cached, so the execution never gets faster as it does with other statements. – Brad Dec 17 '10 at 14:44
@Brad: Dynamic statements get cached as well as any other. As long as you don't change the query text, the cached plan is taken. Of course, different ID-lists here would be a problem if the query is executed very frequently. EXISTS would require some (temporary) table to store the searched IDs in. If you just have the list (i.e. from a web app where the user can select certain employees with checkboxes), IN is still you best option. – TToni Dec 17 '10 at 14:59
I agree that `EXISTS` is not exactly practical here, I simply wanted to point out that it is *preferred* if the table or `IN` list is large. That is good to learn that SQL does cache plans for dynamic SQL (for each change). But I think this caching is largely discountable since the query will change frequently. If not, why would you choose to use dynamic SQL? – Brad Dec 17 '10 at 15:02
1

I hate turning everything red :-( I was hoping there would be another way. Thanks for your input though. It's the correct answer in the end lol. – Since_2008 Dec 17 '10 at 16:35

Brad · Answer 3 · 2010-12-17T15:04:25.307

You're trying to convert not only a string to int, but several ints into one. Do you expect that your SELECT will return all employee's with and ID listed in the array?

I realize that you wanted to do this without a function. However, this is how I currently do it and it works great. Take what you will from my answer.

This code uses a while loop which could likely be improved to a recursive CTE if you are in SQL 2005/2008. You can use the output of the function as a table that you INNER JOIN to which will allow you to filter very quickly.

/*
************************************************************************************************************************
    Name:           ConvertDelimitedListIntoTable
    Description:    Converts a list of delimited values into a table for use like a dynamic IN statment

    Modification History
    Date        Author          Description
    ==========  ============    ====================================
    2009-01-31  B. Williams     Initial Creation

************************************************************************************************************************
*/
ALTER FUNCTION [dbo].[ConvertDelimitedListIntoTable] (
     @list NVARCHAR(MAX) ,@delimiter CHAR(1) )
RETURNS @table TABLE ( 
     item VARCHAR(255) NOT NULL )
AS 
    BEGIN
        DECLARE @pos INT ,@nextpos INT ,@valuelen INT

        SELECT  @pos = 0 ,@nextpos = 1

        WHILE @nextpos > 0 
            BEGIN
                SELECT  @nextpos = CHARINDEX(@delimiter,@list,@pos + 1)
                SELECT  @valuelen = CASE WHEN @nextpos > 0 THEN @nextpos
                                         ELSE LEN(@list) + 1
                                    END - @pos - 1
                INSERT  @table ( item )
                VALUES  ( CONVERT(INT,SUBSTRING(@list,@pos + 1,@valuelen)) )
                SELECT  @pos = @nextpos

            END

        DELETE  FROM @table
        WHERE   item = ''

        RETURN 
    END

Use:

DECLARE @intArray varchar(200)

SELECT *
FROM  tbl_Employee e
      INNER JOIN dbo.ConvertDelimitedListIntoTable(@intArray,',') arr
                 ON e.EmployeeID = arr.Item

There may also be a quick way to do this with a tally table.

Didn't manage to make first solution work in stored procedure. Thanks for this one ! — gilles emmanuel, Aug 27 '14 at 09:23
The brief said not to use a function - though i like your solution not really helpful to the question. — Webezine, Nov 26 '21 at 09:36

t-sql Convert comma separated string into int, without using user created function

3 Answers3

Linked