Is it possible to write a T-SQL query with multiple params for the same column that use AND instead of OR ? (and to do it dynamically)

Question

I have a database with some long varchar and I want to search for specific rows that contain multiple parameters.

So let's say I have article 1 with the long varchar being AVJ4I4LNK45Y0GEHV023.
Then I have a list with the following parameters: AVJ, LNK, GEH.
I want to be able to identify the rows that contain all these parameters.
So basically I would do:

SELECT *

FROM Products

WHERE description like '%AVJ%' AND

      description like '%LNK%' AND

      description like '%GEH%'

This works alright, but the problem is that I don't know how many parameters I'm going to have. Because I extract the parameters from an excel and try to find the rows that match with these parameters.

The only reasonable way I found is CONTAINS(NEAR...) but right now I have SQL SERVER 2014 Express Edition and the full text search feature is not supported with this version.

So right now I'm stuck in t-sql, trying to create a procedure that would add all my {column like '%param%'} in a loop or something but it's not really working.
Is it at least doable in t-sql or am I taking the dead end route ?
I know I'm not even suppose to do that because of SQL Injection but I don't see any other way of doing it.

Any help is welcome,
Thanks !

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

One method would be to construct the query using a values statement and use join and group by:

SELECT p.ProductId
FROM Products p JOIN
     (VALUES ('AVJ'), ('LNK'), ('GEH')) vals(v)
     ON p.description LIKE '%' + vals.v + '%'
GROUP BY p.ProductId
HAVING COUNT(*) = (SELECDT COUNT(*) FROM vals);

You could then generalize this using split() or some other method to convert a string to a table:

WITH vals(v) as (
      SELECT *
      FROM dbo.split(@vals, ',')
     )
    SELECT p.ProductId
    FROM Products p JOIN
         vals
         ON p.description LIKE '%' + vals.v + '%'
    GROUP BY p.ProductId
    HAVING COUNT(*) = (SELECT COUNT(*) FROM vals);

You can Google SQL Server split to get an implementation of a split() function.

Is it possible to write a T-SQL query with multiple params for the same column that use AND instead of OR ? (and to do it dynamically)

Sign up