SQL Server Conversion failed varchar to int

Question

I have a table (no.1) which has 10 columns. One of them clm01 is integer and not allowed with null values.

There is a second table (no.2) which has many columns. One of them is string type clm02. An example of this column data is 1,2,3.

I'd like to make a query like:

select *  
from table1 t1, table2 t2 
where t1.clm01 not in (t2.clm2)

For example in table1 I have 5 records with values in clm01 1,2,3,4,5 and in table2 I've got 1 record with value in clm02 = 1,2,3

So I would like with the query to return only the record with the value 4 and 5 in the clm01.

Instead I get:

Conversion failed when converting the varchar value '1,2,3' to data type int

Any ideas?

not possible. the value you have in t2.clm2 is tested as a united whole. the DB server will **NOT** tear apart that csv value and compare the individual numbers. you should never be storing csv data in a field, especially when you need to deal with individual components of that csv directly — Marc B, Sep 22 '16 at 16:38
You can compare them using LIKE operator but the idea of storing values as CSV is wrong. — Salman A, Sep 22 '16 at 16:50

Unnikrishnan R · Answer 1 · 2016-09-22T17:21:57.537

Use STRING_SPLIT() function to split the comma separated values, if you are using SQL Server 2016.

   SELECT *
   FROM table1 t1
   WHERE t1.clm1 NOT IN (SELECT Value FROM table2 t2
                     CROSS APPLY STRING_SPLIT(t2.clm2,','))

If you are using any lower versions of SQL server write a UDF to split string and use the function in CROSS APPLY clause.

   CREATE FUNCTION [dbo].[SplitString] 
    ( 
      @string NVARCHAR(MAX), 
      @delimiter CHAR(1) 
      ) 
      RETURNS @output TABLE(Value NVARCHAR(MAX) 
     ) 
    BEGIN 
      DECLARE @start INT, @end INT 
      SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
     WHILE @start < LEN(@string) + 1 BEGIN 
      IF @end = 0  
        SET @end = LEN(@string) + 1

        INSERT INTO @output (Value)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
         SET @start = @end + 1 
         SET @end = CHARINDEX(@delimiter, @string, @start)

           END 
           RETURN 
         END

A string splitter is the right thing but PLEASE don't use this one. Splitting strings does NOT require looping. See this article for several options that will blow the doors off this RBAR method for performance. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Sep 22 '16 at 18:54

score 0 · Accepted Answer · edited May 23 '17 at 11:51

I decided to give you a couple of options but this really is a duplicate question I see pretty often.

There are two main ways of going about the problem.

1) Use LIKE to and compare the strings but you actually have to build strings a little oddly to do it:

SELECT *
FROM
    @Table1 t1
WHERE
    NOT EXISTS (SELECT * 
             FROM @Table2 t2
             WHERE ',' + t2.clm02 + ',' LIKE '%,' + CAST(t1.clm01 AS VARCHAR(15)) + ',%')

What you see is ,1,2,3, is like %,clm01value,% you must add the delimiter to the strings for this to work properly and you have to cast/convert clm01 to a char datatype. There are drawbacks to this solution but if your data sets are straight forward it could work for you.

2) Split the comma delimited string to rows and then use a left join, not exists, or not in. here is a method to convert your csv to xml and then split

;WITH cteClm02Split AS (
SELECT
    clm02
FROM
    (SELECT
       CAST('<X>' + REPLACE(clm02,',','</X><X>') + '</X>' AS XML) as xclm02
    FROM
       @Table2) t
       CROSS APPLY (SELECT t.n.value('.','INT') clm02
                 FROM
                t.xclm02.nodes('X') as t(n)) ca
)

SELECT t1.*
FROM
    @Table1 t1
    LEFT JOIN cteClm02Split t2
    ON t1.clm01 = t2.clm02
WHERE
    t2.clm02 IS NULL

OR use NOT EXISTS with same cte

SELECT t1.*
FROM
    @Table1 t1
WHERE
    NOT EXISTS (SELECT * FROM cteClm02Split t2 WHERE t1.clm01 = t2.clm02)

There are dozens of other ways to split delimited strings and you can choose whatever way works for you.

Note: I am not showing IN/NOT IN as an answer because I don't recommend the use of it. If you do use it make sure that you are never comparing a NULL in the select etc. Here is another good post concerning performance etc. NOT IN vs NOT EXISTS

here are the table variables that were used:

DECLARE @Table1 AS TABLE (clm01 INT)
DECLARE @Table2 AS TABLE (clm02 VARCHAR(15))
INSERT INTO @Table1 VALUES (1),(2),(3),(4),(5)
INSERT INTO @Table2 VALUES ('1,2,3')

SQL Server Conversion failed varchar to int

2 Answers2