Tip: Creating an SQL Query that Generates Dynamic LIKE Statements

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)) 

  RETURN
END 
GO

Stored Procedure

CREATE PROC sp_MultipleLikeConds (
 @TableName VARCHAR(50),
 @ColName VARCHAR(50),
 @Pattern VARCHAR(300)
 )

AS
BEGIN
 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)
END
GO

Putting it together:

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

Visit the DevX Tip Bank