Skip to content
Advertisement

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:

Advertisement

Answer

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

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.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement