Filtering on multiple fields using multiple search strings in MSSQL

Question

I'm wondering how (and if) you can use multiple search strings to filter on multiple fields in an effective way. With ordering, so you can ensure that the most important hits are at the top.

If my user types in "mexico maria futter" i want rows where all the three search terms are included somewhere. E.g. ("John Smith", "Donnerfutter", "Maria Mexicobay", "Berlin") would be a hit, but ("John Smith", "Donnerfulter", "Maria Mexicobay", "Berlin") would not.

Edit: If I get multiple hits, I want to be able to order them by what field there was a hit on, e.g. if i specify CustomerName as the most important field:

("Annie Futterson", "Maria's company", "Address field", "Mexico")

("John Smith", "Donnerfutter", "Maria Mexicobay", "Berlin")

where Annie Futterson is on top because there is a match in CustomerName.

SELECT * FROM Customers where (CustomerName LIKE '%futter%' OR ContactName LIKE '%futter%' or Address LIKE '%futter%' or City LIKE '%futter%')
AND (CustomerName LIKE '%mexico%' OR ContactName LIKE '%mexico%' or Address LIKE '%mexico%' or City LIKE '%mexico%')
AND (CustomerName LIKE '%maria%' OR ContactName LIKE '%maria%' or Address LIKE '%bianco%' or City LIKE '%maria%');

The query would need to work on Microsoft SQL Server 2012 and later.

Any help with achieving this functionality would be highly appreciated, even if my question turns out to be a duplicate :)

Edit: i edited the query to produce a working one and added a bit more information. I have no idea how to rank fields by priority, depending on what field there is a hit on.

FullText search with CONTAINS on multiple columns and predicate - AND does not solve my problem, since it does not rank by hit column. It also does not address different options and the effectivity of those.

Show us some sample table data and the expected result - all as formatted text (no images.) [mcve]. — jarlh, Nov 30 '20 at 10:03
Check out this answer that uses SQL Server full text index: https://stackoverflow.com/a/20475748/133665 You can read more about the subject here: https://learn.microsoft.com/en-us/sql/relational-databases/search/full-text-search — HuBeZa, Nov 30 '20 at 10:41

score 1 · Accepted Answer · edited Dec 05 '20 at 12:56

Hmmm . . . One method is to summarize the matches by word and columns and then use that information to filter and order:

SELECT c.*
FROM Customers c CROSS APPLY
     (SELECT COUNT(DISTINCT word) as num_matched_words,
             SUM(CASE WHEN ColName = 'CustomerName' THEN 1 ELSE 0 END) as customername_matches,
             SUM(CASE WHEN ColName = 'ContactName' THEN 1 ELSE 0 END) as contactname_matches,
             SUM(CASE WHEN ColName = 'Address' THEN 1 ELSE 0 END) as addressname_matches
      FROM (VALUES ('CustomerName', @1, CASE WHEN CustomerName LIKE @1 THEN 1 ELSE 0 END),
                   ('CustomerName', @2, CASE WHEN CustomerName LIKE @2 THEN 1 ELSE 0 END),
                   ('CustomerName', @3, CASE WHEN CustomerName LIKE @3 THEN 1 ELSE 0 END),
                   ('ContactName', @1, CASE WHEN ContactName LIKE @1 THEN 1 ELSE 0 END),
                   ('ContactName', @2, CASE WHEN ContactName LIKE @2 THEN 1 ELSE 0 END),
                   ('ContactName', @3, CASE WHEN ContactName LIKE @3 THEN 1 ELSE 0 END),
                   ('Address', @1, CASE WHEN Address LIKE @1 THEN 1 ELSE 0 END),
                   ('Address', @2, CASE WHEN Address LIKE @2 THEN 1 ELSE 0 END),
                   ('Address', @3, CASE WHEN Address LIKE @3 THEN 1 ELSE 0 END)
           ) v(ColName, Word, IsMatch)
      WHERE IsMatch
     ) v
WHERE num_matched_words = 3
ORDER BY customername_matches DESC,
         contactname_matches DESC,
         addressname_matches DESC;

that works :D i still need to loop through the search terms to generate the SQL query, but I guess a working suggestion is much, much better than nothing. do you know anything about how slow this would be? — remote, Nov 30 '20 at 13:35

Filtering on multiple fields using multiple search strings in MSSQL

1 Answers1