Dynamic SQL search improvement

I have this SQL query that I am using for dynamic search in the database:

SELECT [Name],[Vendor]
FROM OrderedApps
Where Name like '%' + 'Microsoft Visio' + '%' OR [Vendor] like '%' + 'Microsoft Visio' + '%'

In the database I have e.g.:

Name         Vendor
Visio Viewer Microsoft
Office Visio Microsoft
Office test  Microsoft

If I provide the input Microsoft Visio

I would like it to list

Name         Vendor
Visio Viewer Microsoft
Office Visio Microsoft

How can I improve my SQL query to achieve this? I have done googling, but haven’t found what I want to do exactly.

Answer

Either pass through your parameter as a table-valued parameter, as a list of words.

Or split it up in SQL:

DECLARE @words TABLE (word varchar(100) PRIMARY KEY);
INSERT @words (word)
SELECT value
FROM STRING_SPLIT(@myparam, ' ');

Then you can unpivot your columns to search, and join it like this:

SELECT [Name],[Vendor]
FROM OrderedApps oa
WHERE EXISTS (
    SELECT 1
    FROM @words w
    LEFT JOIN (VALUES
        (oa.Name),
        (oa.Vendor)
    ) v (col)
        ON v.col LIKE '%' + w.word + '%'
    HAVING COUNT(CASE WHEN v.col IS NULL THEN 1 END) = 0  -- this line ensures that every word matches at least once
);

If you only want any match from the words list, it’s much easier:

WHERE EXISTS (
    SELECT 1
    FROM @words w
    JOIN (VALUES
        (oa.Name),
        (oa.Vendor)
    ) v (col)
        ON v.col LIKE '%' + w.word + '%'
);