How to do comma-separated string to make IN List of strings in the Where clause in sql server ?

CREATE function [dbo].[CommaSeperated_split]
(
@param nvarchar(max), 
@delimiter char(1)
)
returns @table table (val nvarchar(max), seq int)
as
begin
set @param += @delimiter
;with a as
(
select cast(1 as bigint) f, charindex(@delimiter, @param) t, 1 seq
union all
select t + 1, charindex(@delimiter, @param, t + 1), seq + 1
from a
where charindex(@delimiter, @param, t + 1) > 0
)
insert @table
select substring(@param, f, t - f), seq from a
option (maxrecursion 0)
return
end

--Use like this below

SELECT *
FROM yourtable 
WHERE account in (SELECT val FROM dbo.CommaSeperated_split(@account, ','))

About Author: author 4 + years of Information Technology experience in understanding and analyzing the software and hardware engineering requirements, onsite and offshore management of product development. Expert work on design and development for windows,web,mobile Expert knowledge of C#,ADO.NET, ASP.Net MVC, ENTITY FRAMEWORK, LINQ,COLLECTIONS, JAVASCRIPT,AngularJs 1.0,Angular2, Jquery,Jquery-Ajax, SQL SERVER, XAML,Windows phone 8.1 Good knowledge of WCF-Restful services,WCF, WEB API-Services ,Web- Services,MIcrosoft-Azure,JUICE UI,HTML,CSS,AJAX Read More...

Join him on Google+ | Facebook | Linkedin

No comments