Though LIKE is an operator and CONTAINS is a predicate and has several differences between them, they can also be used interchangeably in several use cases. Here we will see how to use both LIKE and CONTAINS to search for a word or phrase and then go through the major differences between them (LIKE Vs CONTAINS).
LIKE & CONTAINS in similar use-case
To start with, here is an example of how to use both LIKE and CONTAINS to get the same result set. For this illustration, I have used the table Application.Cities in Microsoft’s sample database WideWorldImporters.
USE WideWorldImporters
GO
/* LIKE */
SELECT * FROM Application.Cities
WHERE CityName LIKE 'Xenia' OR CityName LIKE 'Yale'
/* CONTAINS */
Select * FROM Application.Cities
WHERE CONTAINS (CityName, '"Xenia" or "Yale"')
GO
LIKE vs CONTAINS
| # | LIKE | CONTAINS |
|---|---|---|
| 1 | LIKE is an operator which is used to find whether a character string matches a specified pattern. | CONTAINS is a predicate which can be used to search for a word, prefix of a word, a word near another word, synonym of a word, etc.. |
| 2 | It does not need the column to be indexed. | It performs full-text search only on full-text indexed columns. |
| 3 | You can use regular characters or wildcard characters like %, _, [] and [^] in a pattern | The only wildcard character which can be used is asterisk (*). |
| 4 | The wildcard characters can be used in different places of the pattern as needed. Example: ‘%Some Phrase%’, ‘_ome Phrase’, ‘[P-S]ome Phrase’, ‘Some P[^a-g]%’, etc.. | The wildcard asterisk (*) will work only if it is used at the end of the word or phrase. Example: ‘”Some Phrase*”‘. |
| 5 | Performance may be slower if you use wildcards at the beginning of the pattern as it may not be able to use any of the indexes. | Since wildcards can be used only at the end, performance should be faster as it can use indexes like the full-text index. |
| 6 | Pattern used for comparison is a string which can have a maximum of eight thousand bytes. | The search condition is nvarchar. Large strings like varcher(max) and nvarchar(max) are not accepted. |
Performance
As mentioned above in the comparison, performance wise, using LIKE will be slower compared to CONTAINS. For example, I ran the above example statements against the Application.Cities table with more than 37,000 records in WideWorldImporters database and obtained the actual execution plan. If you notice the execution plan in the below screen shot, you can see LIKE was using 98% of the execution cost compared with 2% by CONTAINS. This is because CONTAINS used the full text index while LIKE cannot use the index. Thus it is clear that CONTAINS is faster than LIKE.
Reference
- More about LIKE operator at Microsoft Docs.
- More about CONTAINS at Microsoft Docs.