Dynamic SQL search improvement

Question

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.

You seem to want full text search. I suggest you start with the documentation: https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search?view=sql-server-ver15. — Gordon Linoff, Feb 03 '21 at 12:17
Please show us the code you're using. That looks like C# string parameters? How are you passing "Microsoft Visio" to your SQL statement? — n8wrl, Feb 03 '21 at 12:21
`Name like '%' + '{0}' + '%'` isn't valid T-SQL; how is the value of `{0}` being replaced with the search value? I *hope* you aren't injecting. — Thom A, Feb 03 '21 at 12:26
Sorry for the confusion, I updated it with the exact string between `''`, which is `Microsoft Visio`. Yes it is coming from C#, but it is coming in exactly in the form of `Microsoft Visio`. Thank You @GordonLinoff, I will have a look at it — Tobi, Feb 03 '21 at 12:31
You should use parameters, `Name like '%' + @param + '%'`, not concatenating strings in C#. — Charlieface, Feb 03 '21 at 12:34

score 0 · Accepted Answer · answered Feb 03 '21 at 12:42

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 + '%'
);

Excellent! This is what I was looking for basically. Have a good day sir! — Tobi, Feb 03 '21 at 13:26

Dynamic SQL search improvement

1 Answers1