SQL - Search for exact word match in any order

Question

I have spent the entire day looking for an answer to this and have resolved myself to the fact that it's not possible after 3 different search engine variations. Here's my problem. I need to search a table of printer model numbers. Because printer models aren't standardized, different parts of the model could be in different orders.

HP OfficeJet Pro 8715

HP OfficeJet 8715 Pro

Both are the same printer obviously but they show up in both ways in the shared DB between us and vendors. The catch is that if I search HP OfficeJet Pro 8715, it should return HP OfficeJet 8715 Pro as well but it should not return HP OfficeJet Pro 8715dw for example.

In summary, the words can be in any order, but the words must match exactly.

I've already parsed the search string into a #temp table so that each row has a word and this is how I've been comparing to find results.

INSERT INTO #results 
    SELECT Make, Model 
    FROM tDevices 
        WHERE ( Make LIKE '%' + @MAKE + '%' ) AND ( Model LIKE '%' + @MODEL + '%' ) AND ( Model LIKE '%' + @MODEL2 + '%' )

Where each model variable is another word in the model. This works great but returns HP OfficeJet Pro 8715 as well as HP OfficeJet Pro 8715dw

Is this possible?

Thanks

If the `Make` is always at the start just check explicitly against all permutations of the order of words in `Model`? — Sash Sinha, Feb 07 '17 at 23:12
Can you make this an answer? This ended up being the best solution for us. — Clark McCauley, Feb 08 '17 at 17:34

score 0 · Answer 1 · edited May 23 '17 at 12:33

0

It returns 8715dw, because the % (anything operator) lets it.

Try without the %s:

INSERT INTO #results 
    SELECT Make, Model 
    FROM tDevices 
        WHERE ( Make LIKE @MAKE ) AND ( Model LIKE @MODEL ) AND ( Model LIKE  @MODEL2 )

Update (based on https://stackoverflow.com/a/14291100/510788):

SELECT * FROM
  (SELECT Make, CONCAT(' ', REPLACE(Model,' ','  '), ' ') AS Model FROM tDevices) t
WHERE
   Make  LIKE '%hp%' AND (
       Model LIKE '% 8715 %' OR
       Model LIKE '% pro %' AND
       (REPLACE(REPLACE(Model,
       ' 8715 ',''),
       ' pro ','')) = ''
   )

edited May 23 '17 at 12:33

Community

1
1

answered Feb 07 '17 at 23:24

Kalmár Gábor

434
4
10

The catch being that without the %s, the search no longer ignores the position of the models right? – Clark McCauley Feb 08 '17 at 00:05
Yes, but I wasn't thinking it through, sorry. See my update for a better possible solution. – Kalmár Gábor Feb 08 '17 at 14:03
Very interesting. It won't solve the whole problem (as it looks like additional words would be included in the search) but I see your point and it's exactly what I need for the first part of the query. Thank you! I'll get it working and hit you up with an answer! – Clark McCauley Feb 08 '17 at 16:58

SQL - Search for exact word match in any order

1 Answers1