0

I have a requirement where I need to search a few columns in the table to see if the letter or word passed in as parameter is contained in any of those columns and return the first 100 rows. For example:

select id, col1, col2, col3
from table
where col1 like '%word%'
or col2 like '%word%'
or col3 like '%word%'
order by col1
offset 50 rows 
fetch next 100 rows only

I know that using a % as the beginning and end of a like operation will result in index scan. Is there a better way to write these kinds of queries? Thanks!

Improve this question
5
  • Does it have to be a "full contains" type of search? As it's much easier to optimize a check for "starts with or ends with". E.g. '%this' or 'this%' is easier to optimize than '%this%'.
    – J.D.
    Jun 9 at 18:43
  • @J.D. It has to be a full contains type of search, unfortunately.
    – JoeCharles
    Jun 9 at 18:44
  • No problem! There are other solutions for full contains search. They're just more involved.
    – J.D.
    Jun 9 at 18:45
  • Thank you for your time! I'm looking forward to the solutions.
    – JoeCharles
    Jun 9 at 18:48
  • 1
    Try a search for sql server & trigrams
    – Stephen Morris - Mo64
    Jun 9 at 20:54

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.