Dynamic LIKE in WHERE clause in T-SQL with STUFF and FOR XML Path

What I’m trying to do is search a text column for anything LIKE anything in a list of values.

The table with the text column is very large, so I can’t join on LIKE '%' + valuename + '%' (the list of values temp table is not terribly large).

I thought I would try to do it this way, but I need to get the single quotes around the % in the dynamic WHERE clause. Maybe this is not possible.

Here is an example:

CREATE TABLE #SearchValues (valuename VARCHAR(20))

INSERT INTO #SearchValues
VALUES ('Red235'), ('Blue678'), ('2Purple'), ('63Cyan'),('99Black')

CREATE TABLE #TextTable (textfield VARCHAR(300))

INSERT INTO #TextTable
VALUES ('"Red235"'), ('blah and [99Black]'), ('something else'), ('n'),
       ('63Cyan'), ('other text'), ('nothing'), ('[2Purple]'), ('')

SELECT 
    -- this would be it except that it needs the single quotes around the %
    WhereClauseIWant = STUFF((SELECT ' OR valuename LIKE %' + valuename + '%'
                              FROM #SearchValues
                              FOR XML PATH('')), 1, 19, '') 

SELECT *
FROM #TextTable
WHERE textfield LIKE STUFF((SELECT ' OR valuename LIKE ' + '%' + valuename + '%'
                            FROM #SearchValues
                            FOR XML PATH('')), 1, 19, '')

Answer

Ideally you should just use an EXISTS predicate in a static query.

SELECT *
FROM #TextTable T
WHERE EXISTS (SELECT 1
    FROM #SearchValues s
    WHERE t.textfield LIKE '%' + s.valuename + '%'
);

If you are really set on using dynamic SQL, you obviously need EXEC, and you need to do this safely. You also had some typos.

Note the use of QUOTENAME to inject safely, and .value to prevent XML entitization.

DECLARE @sql nvarchar(max) = N'
SELECT *
FROM #TextTable
WHERE 1=0
' + (
    SELECT '
OR textfield LIKE ' + QUOTENAME('%' + valuename + '%', '''')
    FROM #SearchValues
    FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)');

PRINT @sql;  -- for testing
EXEC sp_executesql @sql;