The second (assuming you means CONTAINS, and actually put it in a valid query) should be faster, because it can use some form of index (in this case, a full text index). Of course, this form of query is only available if the column is in a full text index. If it isn't, then only the first form is available.
The first query, using LIKE, will be unable to use an index, since it starts with a wildcard, so will always require a full table scan.
The CONTAINS query should be:
SELECT * FROM table WHERE CONTAINS(Column, 'test');
Top answer 1 of 5
224
The second (assuming you means CONTAINS, and actually put it in a valid query) should be faster, because it can use some form of index (in this case, a full text index). Of course, this form of query is only available if the column is in a full text index. If it isn't, then only the first form is available.
The first query, using LIKE, will be unable to use an index, since it starts with a wildcard, so will always require a full table scan.
The CONTAINS query should be:
SELECT * FROM table WHERE CONTAINS(Column, 'test');
2 of 5
18
Having run both queries on a SQL Server 2012 instance, I can confirm the first query was fastest in my case.
The query with the LIKE keyword showed a clustered index scan.
The CONTAINS also had a clustered index scan with additional operators for the full text match and a merge join.
W3Schools
w3schools.com › sql › sql_like.asp
SQL LIKE Operator
SELECT * FROM Customers WHERE ... ... To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase....
SQLServerCentral
sqlservercentral.com › forums › topic › difference-between-like-operator-and-contains-operator
Difference between Like operator and Contains operator – SQLServerCentral Forums
November 18, 2008 - First, examine the execution plans between these two statements. You should see quite a difference. Second, using CONTAINS(textField, '"Cats and Dogs"') is the bare minimum format. For a "google-like" search that returns both words try Contains( textField, "Cat" OR "Dog").
DataCamp
datacamp.com › tutorial › sql-contains
SQL CONTAINS: A Comprehensive Tutorial | DataCamp
February 9, 2024 - For example, to find any product name containing "apple": SELECT * FROM Products WHERE productName LIKE '%apple%' SQL provides functions like LEFT and REVERSE for extracting or manipulating parts of text strings, offering extensive capabilities for text analysis and manipulation.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › language-elements › like-transact-sql
LIKE (Transact-SQL) - SQL Server | Microsoft Learn
The following table shows several examples of using the LIKE keyword and the [ ] wildcard characters. You can search for character strings that include one or more of the special wildcard characters. For example, the discounts table in a customers database may store discount values that include a percent sign (%). To search for the percent sign as a character instead of as a wildcard character, the ESCAPE keyword and escape character must be provided. For example, a sample database contains a column named comment that contains the text 30%. To search for any rows that contain the string 30% anywhere in the comment column, specify a WHERE clause such as WHERE comment LIKE '0!%%' ESCAPE '!'. If ESCAPE and the escape character aren't specified, the Database Engine returns any rows with the string 30!.
DB Vis
dbvis.com › thetable › sql-contains-function-sql-server-guide-with-examples
How to Use CONTAINS in SQL Server: Guide With Examples
February 17, 2025 - The SQL CONTAINS function works on columns of the following types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, and varbinary(max). The columns in the CONTAINS clause must come from a single table and be full-text indexed. The LIKE vs CONTAINS SQL Server comparison boils down to a few facts:
My Tec Bits
mytecbits.com › home › microsoft › sql server › like vs contains in sql server
LIKE vs CONTAINS in SQL Server | My Tec Bits
November 11, 2021 - 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.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › queries › contains-transact-sql
CONTAINS (Transact-SQL) - SQL Server | Microsoft Learn
CONTAINS is a predicate used in the WHERE clause of a Transact-SQL SELECT statement to perform SQL Server full-text search on full-text indexed columns containing character-based data types.
Snowflake Documentation
docs.snowflake.com › en › sql-reference › functions › contains
CONTAINS | Snowflake Documentation
CONTAINS( <expr1> , <expr2> ) Copy · expr1 · The string to search in. expr2 · The string to search for. Returns a BOOLEAN or NULL: Returns TRUE if expr2 is found inside expr1. Returns FALSE if expr2 is not found inside expr1. Returns NULL if either input expression is NULL. For comparisons that match a string against more than one specified pattern, you can use the following functions: ILIKE ANY · LIKE ALL ·
Mimo
mimo.org › glossary › sql › contains
SQL CONTAINS: Syntax, Usage, and Examples
The CONTAINS keyword allows you to check whether a specific word or phrase exists in a column that has full-text indexing enabled. It is used primarily in SQL Server and Oracle. Unlike basic string comparisons, CONTAINS supports natural language searches and can evaluate multiple keywords, ...
W3Schools
w3schools.com › sql › sql_ref_like.asp
SQL LIKE
The following SQL statement selects all customers with a CustomerName that starts with "a" and are at least 3 characters in length: SELECT * FROM Customers WHERE CustomerName LIKE 'a__%'; Try it Yourself »
GoLinuxCloud
golinuxcloud.com › home › sql › sql contains explained [practical examples]
SQL CONTAINS Explained [Practical examples] | GoLinuxCloud
October 15, 2023 - CONTAINS is used in SQL to perform full-text search in a column. It searches for the specified words or phrases within a targeted column. For instance, to find rows in an Articles table where the Content column contains the word "SQL", you would ...
TechOnTheNet
techonthenet.com › sql › like.php
SQL: LIKE Condition
Because the LIKE condition is not case-sensitive, the following SQL statement would return the same results: Try It · SELECT * FROM customers WHERE last_name LIKE 'j%' ORDER BY last_name; You can also using the % wildcard multiple times with the LIKE condition. Using the same customers table with the following data: Let's try to find all last_name values from the customers table where the last_name contains the letter 'e'. Enter the following SQL statement: Try It
Enterprise DNA
blog.enterprisedna.co › sql-contains-string
SQL Contains String: Guide With Examples – Master Data Skills + AI
The % character is a wildcard in SQL that matches any sequence of characters, including an empty sequence. So, for example, if productName contained the values “apple”, “green apple”, “apple pie”, “pineapple”, and “orange”, the query would return the rows with “apple”, “green apple”, “apple pie”, and “pineapple”, because all of these contain the string apple. It’s important to note that like CONTAINS, the LIKE operator is case-insensitive in some SQL databases, so it would match “Apple”, “APPLE”, or any other mix of case.
Reddit
reddit.com › r/sql › contains vs like giving different results?
r/SQL on Reddit: Contains VS Like Giving Different Results?
March 13, 2020 -
I have been doing some tinkering with full text indexes in SQL Server 2019. I've found that I am getting different results when compared to a 'like' statement.
Here is my example code. I am using StackOverflow2010 sample database.
-- create full text catalog and index CREATE FULLTEXT CATALOG fulltextCatalog AS DEFAULT; go CREATE FULLTEXT INDEX ON dbo.Posts(Tags) KEY INDEX PK_Posts__Id WITH STOPLIST = SYSTEM; go -- wait a while after running this, then run the selects select count(*) as ContainsResults from Posts where contains(Tags, 'sql') select count (*) as LikeResults from Posts as p where Tags like '%sql%'
Results:
ContainsResults 59620 LikeResults 94039
Execution Plan:
This data looks like this:
select top 10 Tags from Posts where Tags like '%sql%' Tags <mysql><database><binary-data><data-storage> <mysql><database><triggers> <sql><sql-server><datatable><rdbms> <php><sql><database><flat-file> <c#><sql><vb.net><ascii><hex> <sql><asp.net><xml><sitemap> <mysql><sql-server><csv><sql-server-2005><bcp> <sql-server><database><svn><version-control> <sql-server><migration> <mysql><sql-server><sql-server-2005>
Top answer 1 of 2
1
i mean, these are 2 different functions/functionalities doing 2 different things. Why is the fact that results aren't the same surprising or unexpected? Or are you doing a confirmation study?
2 of 2
1
iirc, CONTAINS looks for words that match (ignoring punctuation) not patterns that match, so it would match 'sql', '', and '' but not ''. It can also do a couple of other things like prefix matching, but I don't think you can get it to do a partial word or pattern match aside from that.
GeeksforGeeks
geeksforgeeks.org › sql › sql-select-where-field-contains-words
SQL SELECT WHERE Field Contains Words - GeeksforGeeks
July 23, 2025 - We want to select products where the product name contains the word "chair". ... CREATE TABLE products ( product_id INT, product_name VARCHAR(100) ); INSERT INTO products (product_id, product_name) VALUES (1, 'Office Chair'), (2, 'Dining Chair'), (3, 'Desk Lamp'), (4, 'Armchair'); SELECT * FROM PRODUCTS; ... Step 1: Basic SQL SELECT statement without any conditions. ... Step 2: SQL SELECT statement using the LIKE operator to filter rows where the product name contains the word "chair".
UniversalClass
universalclass.com › articles › computers › sql › using-the-in-not-and-like-operators-in-sql.htm
Using the IN, NOT, and LIKE Operators in SQL
The LIKE operator uses the wildcard % character. The % character can be placed at the beginning, end or within your string value. Note that the % operator is for string or varchar values. The above examples use the percent character to return values that start with a specific string (in the ...
RisingWave
risingwave.com › blog › effective-sql-string-contains-methods-for-databases
Effective SQL String Contains Methods for Databases - RisingWave: Real-Time Event Streaming Platform
In SQL Server, the LIKE clause is commonly employed to perform pattern matching operations on strings. This clause enables developers to check if a column contains a specific sequence of characters or substrings based on defined patterns using ...
freeCodeCamp
freecodecamp.org › news › sql-where-contains-string-substring-query-example
SQL Where Contains String – Substring Query Example
March 23, 2023 - SELECT * FROM products_data WHERE product_name LIKE 'lap%' ... CHARINDEX() is an SQL server function for finding the index of a substring in a string.
Sentry
sentry.io › sentry answers › sql › how to select in sql where the field contains words?
How to SELECT in SQL where the field contains words? | Sentry
To use partial matching instead of exact matching in SQL, we use the LIKE keyword. Here is an example query and its result. ... SELECT * FROM Poem WHERE Line LIKE '%ow%'; -- Row, row, merrily row your boat, -- Dream gently down the stream.