racer sql imported from SE
sql-server sql-server-2008-r2
[In this question][1] he's having the same problem as I am. I need something like:

    select * from blablabla 
    where product 
    like '%rock%' or
    like '%paper%' or
    like '%scisor%' or
    like '%car%' or
    like '%pasta%' 

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

[This question talks about Full Text as well][2]

  [1]: https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server
  [2]: https://stackoverflow.com/questions/6506418/sql-searching-multiple-words-in-a-string
Top Answer
Erik Darling
Full text indexes generally aren't a magic bullet, and require additional maintenance, disk space, and fairly intrusive changes to query patterns. 

Unless you're truly in need of indexing large documents (think email bodies, PDFs, Word docs, etc.), they're overkill (and if we're being honest, I'd take that process out of SQL Server entirely and use Elasticsearch or something similar).

For smaller use-cases, computed columns are generally a better approach.

Here's a quick demo setup:

    use tempdb
    CREATE TABLE #fulltextindexesarestupid (Id INT PRIMARY KEY CLUSTERED, StopAbusingFeatures VARCHAR(100))
    INSERT #fulltextindexesarestupid (Id)
    FROM sys.messages AS m
    CROSS JOIN sys.messages AS m2
    UPDATE #fulltextindexesarestupid
    SET StopAbusingFeatures = CASE WHEN Id % 15 = 0 THEN 'Bad'
    							   WHEN Id % 3 = 0 THEN 'Idea'
    							   WHEN Id % 5 = 0 THEN 'Jeans'
    ALTER TABLE #fulltextindexesarestupid 
    ADD LessBad AS CONVERT(BIT, CASE WHEN StopAbusingFeatures LIKE '%Bad%' THEN 1
    					WHEN StopAbusingFeatures LIKE '%Idea%' THEN 1
    					ELSE 0 END)
    CREATE UNIQUE NONCLUSTERED INDEX ix_whatever ON #fulltextindexesarestupid (LessBad, Id)

Querying based on even a non-persisted column gives us a plan that 'uses indexes' and everything :)
    FROM #fulltextindexesarestupid AS f
    WHERE LessBad = 1


  [1]: https://i.stack.imgur.com/BJwhp.jpg
Better approach for “LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE”

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.