SQL Server Conversion failed varchar to int -

i have table (no.1) has 10 columns. 1 of them clm01 integer , not allowed null values.

there second table (no.2) has many columns. 1 of them string type clm02. example of column data 1,2,3.

i'd make query like:

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

for example in table1 have 5 records values in clm01 1,2,3,4,5 , in table2 i've got 1 record value in clm02 = 1,2,3

so query return record value 4 , 5 in clm01.

instead get:

conversion failed when converting varchar value '1,2,3' data type int

any ideas?

i decided give couple of options duplicate question see pretty often.

there 2 main ways of going problem.

1) use , compare strings have build strings little oddly it:

select *     @table1 t1     not exists (select *               @table2 t2              ',' + t2.clm02 + ',' '%,' + cast(t1.clm01 varchar(15)) + ',%') 

what see ,1,2,3, %,clm01value,% must add delimiter strings work , have cast/convert clm01 char datatype. there drawbacks solution if data sets straight forward work you.

2) split comma delimited string rows , use left join, not exists, or not in. here method convert csv xml , split

;with cteclm02split ( select     clm02     (select        cast('<x>' + replace(clm02,',','</x><x>') + '</x>' xml) xclm02            @table2) t        cross apply (select t.n.value('.','int') clm02                                  t.xclm02.nodes('x') t(n)) ca )  select t1.*     @table1 t1     left join cteclm02split t2     on t1.clm01 = t2.clm02     t2.clm02 null 

or use not exists same cte

select t1.*     @table1 t1     not exists (select * cteclm02split t2 t1.clm01 = t2.clm02) 

there dozens of other ways split delimited strings , can choose whatever way works you.

note: not showing in/not in answer because don't recommend use of it. if use make sure never comparing null in select etc. here post concerning performance etc. not in vs not exists

here table variables used:

declare @table1 table (clm01 int) declare @table2 table (clm02 varchar(15)) insert @table1 values (1),(2),(3),(4),(5) insert @table2 values ('1,2,3')