How to Query SQL Server Full-Text Search Using CONTAINS and FREETEXT
Leave a LIKE on YouTube if you found this tutorial useful and SUBSCRIBE for more coding videos.
Speaking of LIKE, in this coding lesson I show some different methods of searching SQL Server tables using LIKE %%, CONTAINS, FREETEXT, CONTAINSTABLE and FREETEXTTABLE.
If you’re searching a table and your query is a single word then CONTAINS is best. For short queries CONTAINS is also pretty good but you need to do some pre-processing of the query if it contains spaces.
FREETEXT is better for longer queries but the results can be less precise.
If you want to know the rank score of each row returned by CONTAINS or FREETEXT then in the video I show how to use the SQL Server CONTAINSTABLE and FREETEXTTABLE functions.
It’s generally best to avoid LIKE queries unless your table doesn’t have many rows.
Note that full text indexing is very database specific so you’ll need to use something else for Oracle, PostgreSQL and MySQL.
If you can’t use Full Text Indexing on your SQL Server database then make sure it’s installed. If the Full-Text index menu option is greyed out in your SQL Server Management Studio then you’ll definitely need to add Full-Text indexing services to your installation.
