sql server - Select IN using varchar string with comma delimited values -

i trying search several tables list of phones.

the problem converting single string valid comma delimited string use in conjunction in clause.

i tried using replace fix problem.

declare @phonenumber varchar(3000)  set @phonenumber = '6725556666,2124444444' set @phonenumber = '''' + @phonenumber + ''''  select @phonenumber '6725556666','2124444444' 

finally sample sql not recognize string expected:

select  provider         ,phonenumber               ,changetype         ,changedate  dbo.phonelog phonenumber in (@phonenumber) 

there several ways handle this. 1 option use dynamic sql , inject phone number string variable containing statement , executing this:

declare @phonenumber varchar(3000)  set @phonenumber = '6725556666,2124444444' declare @sql nvarchar(max) set @sql = n'     select provider, phonenumber, changetype, changedate      dbo.phonelog     phonenumber in (' + @phonenumber + ')' exec sp_executesql @sql 

please note approach can vulnerable sql injection attacks, instance feeding string

set @phonenumber = '1);truncate table phonelog;--' 

would empty table. using dynamic sql approach above should option if it's string fed in injected sanitized , safe (or maybe should never used).

another, possibly better, option use user defined function split phonenumber variable , use this:

select provider, phonenumber, changetype, changedate  dbo.phonelog phonenumber in (     select splitdata dbo.fnsplitstring(@phonenumber,',')     -- add check here data returned function      -- indeed numeric , valid     -- isnumeric(splitdata) = 1     ) 

here's function used in example:

create function [dbo].[fnsplitstring]        (            @string nvarchar(max),            @delimiter char(1)        )        returns @output table(splitdata 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 @output (splitdata)                 values(substring(@string, @start, @end - @start))                set @start = @end + 1                set @end = charindex(@delimiter, @string, @start)            end            return        end  

i did not write function, think got somewhere on internet...