Sometimes it can be quite a tedious task to construct a simple SQL query. These queries usually includes a lot of repetition for similar, yet different results. If you take for example, a query that needs 5 different LIKE conditions, it is not so bad, but what about 20 different LIKE conditions?

That is the problem. In order to construct a query that can generate dynamic LIKE statements, you need a Function, and a Stored Procedure.

Function

CREATE FUNCTION dbo.SplitStatements(@strInput NVARCHAR(MAX), @Delimiter VARCHAR(1) ) RETURNS @tmpTable TABLE (nr INT IDENTITY(1, 1), token NVARCHAR(MAX) ) AS BEGIN  DECLARE @Pos INT  DECLARE @FoundPos INT   SET @Pos = 1  SET @FoundPos = CHARINDEX(@Delimiter, strInput)  WHILE (@FoundPos > 0)  BEGIN     INSERT INTO @tmpTable (token)    SELECT LTRIM(RTRIM(SUBSTRING(@strInput, 1, @FoundPos - 1)))     SET @strInput = SUBSTRING(@strInput, @FoundPos + 1, LEN(@strInput) - @FoundPos)    SET @Pos = @Pos + 1     SET @FoundPos = CHARINDEX(@Delimiter, @strInput)  END  INSERT INTO tmpTable (token)  SELECT LTRIM(RTRIM(@strInput))   RETURNEND GO

Stored Procedure

CREATE PROC sp_MultipleLikeConds ( @TableName VARCHAR(50), @ColName VARCHAR(50), @Pattern VARCHAR(300) )ASBEGIN DECLARE @strSQL VARCHAR(500) SET NOCOUNT ON SET @strSQL = CONCAT (   'SELECT * FROM '   ,@TableName   ,' as T Where Exists (Select * from dbo.SplitStatements('   ,''''   ,@Pattern   ,''''   ,','   ,''''   ,','   ,''''   ,') as P '   ,'Where T.'   ,@ColName   ,' LIKE '   ,''''   ,'%'   ,''''   ,'+ P.token + '   ,''''   ,'%'   ,''''   ,')'   ) PRINT @strSQL EXEC (@strSQL)ENDGO

Putting it together:

EXEC sp_MultipleLikeConds @TableName = 'YourTableName', @ColName = 'YourColumnName', @Pattern = 'LIKE_Term_1, LIKE_Term_2, LIKE_Term_3, LIKE_Term_4, etc.'