Split the delimited string (Used in In clause)

 SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF OBJECT_ID('dbo.f_split') IS NOT NULL

   DROP Function dbo.f_split

GO

Create function [dbo].[f_split]

(

   @Param Nvarchar(max) 

 , @Delimiter Nchar(1)

)

Returns @t 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 @t

   Select Rtrim(Ltrim(Substring(@Param, f, t - f))), seq From a Option (maxrecursion 0)

   Return

End

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

Test Code:

select 'success' where 'a' in (select val from dbo.f_split('a b c',' '))