[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
TopAnswers
Databases
Top Answer
meme (imported from SE)
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)
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (@@ROWCOUNT))
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'
END
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 :)
SELECT COUNT(*)
FROM #fulltextindexesarestupid AS f
WHERE LessBad = 1
[![NUTS][1]][1]
[1]: https://i.stack.imgur.com/BJwhp.jpg