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:
WHERE description like '%AVJ%' AND
description like '%LNK%' AND
description like '%GEH%'
One method would be to construct the query using a
values statement and use
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