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