Way to Check Multiple LIKE without dynamic SQL

,

This uses CROSS APPLY and the fn_split() function to separate out parameters. The @vParam parameter stores your LIKE conditions, separated by commas.

Declare @vTable TABLE(id INT, NAME VARCHAR(100))
INSERT INTO @vTable
    SELECT 1,'Shamas Qamar' UNION ALL
    SELECT 2,'Atif' UNION ALL
    SELECT 3,'Kashif' UNION ALL
    SELECT 4,'Imran' 

DECLARE @vParam VARCHAR(100)
-- To check the values with LIKE operator. These are comma separated.
SET @vParam = 'Sha,hif'

-- Used CROSS APPLY to accomplish the task...
SELECT * FROM @vTable 
CROSS APPLY (SELECT [value] FROM dbo.fnSplit(@vParam,',')) b
WHERE NAME LIKE '%' + b.[VALUE] + '%'

Rate

2.36 (14)

Share

Share

Rate

2.36 (14)