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.'