SQL - Why use the contains predicate over the like predicate?

Question

I just started a new job and need to modify a SQL stored procedure that queries people in a users table. The query is:

SELECT * FROM users_table WHERE CONTAINS(Last_Name,'var_for_Last_Name*')

A bug occurs if someone's last name contains a noise word. For example, one of the users has a last name of "See Jones". Since "see" is a noise word, it throws the query off and doesn't return any records.

My question is, why do you think the person who created this query used the contains predicate instead of the like predicate?

What are the benefits of using CONTAINS to search last names over using LIKE?

Can I change the query to "WHERE Last_Name like '%var_for_last_name%' and still produce the same results, minus the noise word bug?

Answer 1

its not about case sensitivity. This procedure uses full-text search.



Full-Text search offers many benefits over a LIKE predicate. It allows fast and efficient querying when you have large amounts of unstructured data.



It is many orders of magnitude faster than a LIKE operator; especially for larger tables. This is because when you use the LIKE operator it does a byte-by-byte search through the contents of a row looking for a match to the search phrase. SQL FTS references the full text index to instantly return a list of matches. SQL FTS also supports a large number of different languages and language characters. As well as accents, it also seamlessly handles: compound words (in German and Chinese) and compound characters which occur in Chinese.



You already have the full-text index in your database, but you can read the following for additional info:

http://www.databasejournal.com/features/mssql/article.php/1438211





http://msdn2.microsoft.com/en-us/library/aa214780(SQL.80).aspx

Answer 2

CONTAINS

Is a predicate used to search columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINS can search for:



A word or phrase.





The prefix of a word or phrase.





A word near another word.





A word inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).





A word that has a higher designated weighting than another word.

Answer 3

I honestly don't think there is a major difference. My question is why wouldn't LIKE '%...%' produce the same error with your See Jones example?

Answer 4

I would go for the LIKE because CONTAINS is case sensitive