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.
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.
SQL SELECT WHERE field contains words - Stack Overflow
I need a select which would return results like this: SELECT * FROM MyTable WHERE Column1 CONTAINS 'word1 word2 word3' And I need all results, i.e. this includes strings with 'word2 word3 word1' or ' More on stackoverflow.com
SQL search contains a phrase with number and text
How are you doing the like? You'll need to filter by using Where column_name like '%w000014%'; This will show the rows where that text is anywhere in the column. The percentage characters say to search for the string anywhere in the row. If you have just a % at the beginning of the search text then it will show all rows which start with that string value. More on reddit.com
2
1
July 9, 2021How to search for reserved SQL words in CONTAINS?
I'm not familiar with Oracle Text, but wouldn't it be easier to just quote all the words? More on reddit.com
7
3
March 29, 2023How do I use "Contains" in SQL?
Presumably you are using the default HSQL database? From the relevant documentation : The LIKE keyword uses '%' to match any (including 0) number of characters, and '' to match exactly one character. To search for '%' or '' itself an escape character must also be specified using the ESCAPE clause. For example, if the backslash is the escaping character, '%' and '_' can be used to find the '%' and '_' characters themselves. For example, SELECT .... LIKE '_%' ESCAPE '' will find the strings beginning with an underscore. So something like SELECT TITLE, FORMAT FROM YOURTABLE WHERE FORMAT LIKE "%PS2%" More on reddit.com
9
1
June 16, 2024What is the SQL CONTAINS Command Used For?
The SQL CONTAINS command is used for performing full-text searches within a text column in a database table. It allows you to search for words, phrases, or even formats where certain words are near each other. It's particularly useful in large databases where you need to search through extensive text data efficiently.
ituonline.com
ituonline.com › itu online › blogs › sql contains command : a powerful sql search option
SQL CONTAINS Command : A Powerful SQL Search Option - ITU Online ...
Do I Need to Index My Table to Use SQL CONTAINS?
Yes, to use the CONTAINS command effectively, you need to have a full-text index on the column(s) you want to search. A full-text index is a special type of index that is specifically designed to allow fast and efficient searching of text within a column. Setting up a full-text index can be a complex process and requires additional storage, but it significantly improves the performance of full-text searches in large datasets.
ituonline.com
ituonline.com › itu online › blogs › sql contains command : a powerful sql search option
SQL CONTAINS Command : A Powerful SQL Search Option - ITU Online ...
Is the SQL CONTAINS Command Case-Sensitive?
The case-sensitivity of the CONTAINS command depends on the collation settings of the database system you are using. Some database systems, like SQL Server, provide options to perform case-sensitive or case-insensitive searches. It's essential to check the documentation of your specific database system to understand how case sensitivity is handled.
ituonline.com
ituonline.com › itu online › blogs › sql contains command : a powerful sql search option
SQL CONTAINS Command : A Powerful SQL Search Option - ITU Online ...
Videos
05:15
select column contains word in sql server - YouTube
03:08
How to detect string has numbers in SQL Server - YouTube
11:30
How to Query SQL Server Full-Text Search Using CONTAINS and FREETEXT ...
80. CONTAINS set operation in SQL - YouTube
06:42
pyodbc the sql contains 0 parameter markers but 1 parameters were ...
W3Schools
w3schools.com › sql › sql_like.asp
SQL LIKE Operator
To return records that contains a specific letter or phrase, add the % both before and after the letter or phrase.
DataCamp
datacamp.com › tutorial › sql-contains
SQL CONTAINS: A Comprehensive Tutorial | DataCamp
February 9, 2024 - SELECT * FROM your_table WHERE CONTAINS(your_column, '"compute*"'); This approach is particularly useful when you're looking to capture a broad set of data related to a root word or when you're unsure of the exact terms used in the database. For scenarios requiring even more sophisticated pattern matching, consider combining SQL CONTAINS with the LIKE operator or regular expressions.
IBM
ibm.com › docs › en › db2-for-zos › 12.0.0
Db2 12 - Db2 SQL - CONTAINS function scalar function
The CONTAINS function searches a text search index using criteria that are specified in a search argument and returns a result about whether or not a match was found.
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 - You can use CONTAINS to get all records that do not match some criteria with: ... Note that the “Laptop” device has been excluded by the results. Et voilà! You are now a SQL CONTAINS master.
Oracle
docs.oracle.com › en › database › other-databases › nosql-database › 25.3 › sqlreferencefornosql › contains_function.html
SQL Reference Guide
October 13, 2025 - The contains function indicates whether or not a search string is present inside the source string.
Udemy
blog.udemy.com › home › the secrets of the sql contains command
The Secrets of the SQL CONTAINS Command - Udemy Blog
December 16, 2021 - SELECT DISTINCT TOP 10 phrase AS Result FROM phrases_table WHERE CITY = 'New York City' AND CONTAINS ( phrase, 'a*' ); The * in the substring argument is a wildcard placeholder representing all the other characters in a word. We do this because, without it, SQL Server wouldn’t return anything.
ITU Online
ituonline.com › itu online › blogs › sql contains command : a powerful sql search option
SQL CONTAINS Command : A Powerful SQL Search Option - ITU Online IT Training
January 26, 2024 - The SQL CONTAINS command is a powerful tool used in conjunction with the WHERE clause to filter rows based on a search condition. Primarily used in full-text
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
August 15, 2023 - 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. -- Life is but a dream boat down the stream. The % sign is a wildcard, meaning that any text can be on either side of the letters ow. So this query will match down and row, and the rest of the containing line.
Databricks
docs.databricks.com › reference › sql language reference › functions › built-in functions › alphabetical list of built-in functions › contains function
contains function | Databricks on AWS
April 18, 2024 - > SELECT contains(NULL, 'Spark'); NULL > SELECT contains('SparkSQL', NULL); NULL > SELECT contains('SparkSQL', 'Spark'); true > SELECT contains('SparkSQL', 'ark'); true > SELECT contains('SparkSQL', 'SQL'); true > SELECT contains('SparkSQL', 'Spork'); false > SELECT contains('SparkSQL', ''); true > SELECT contains(x'120033', x'00'); true
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.
W3Schools
w3schools.com › sql › sql_wildcards.asp
SQL Wildcard Characters
Return all customers that contains the pattern 'mer':
Snowflake Documentation
docs.snowflake.com › en › sql-reference › functions › contains
CONTAINS | Snowflake Documentation
+-----------------------------------------+-----------------------------------------+ | CONTAINS(COLLATE('Ñ', 'EN-CI-AI'), 'N') | CONTAINS(COLLATE('Ñ', 'ES-CI-AI'), 'N') | |-----------------------------------------+-----------------------------------------| | True | False | +-----------------------------------------+-----------------------------------------+
W3Schools
w3schools.com › sql › sql_any_all.asp
SQL ANY and ALL Operators
SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL Order By SQL And SQL Or SQL Not SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Aggregate Functions SQL Min and Max SQL Count SQL Sum SQL Avg SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Union All SQL Group By SQL Having SQL Exists SQL Any, All SQL Select Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL Comments SQL Operators
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.
InterSystems
docs.intersystems.com › latest › csp › docbook › DocBook.UI.Page.cls
%CONTAINS | Caché SQL Reference | Caché & Ensemble 2018.1.4 – 2018.1.11
The %CONTAINS predicate is one of the few predicates that can be used on a stream field in a WHERE clause. The available languages are English, French, German, Italian, Japanese, Portuguese, and Spanish.
Scaler
scaler.com › topics › sql-contains-query
What is SQL CONTAINS Query? - Scaler Topics
May 3, 2023 - CONTAINS is a powerful predicate commonly used in conjunction with a WHERE clause and a SELECT statement to execute queries. It enables the SQL Server to search for full text within an indexed column of a specified database.
Microsoft Learn
learn.microsoft.com › en-us › sql › relational-databases › system-functions › containstable-transact-sql
CONTAINSTABLE (Transact-SQL) - SQL Server | Microsoft Learn
October 21, 2025 - Returns a table of zero, one, or ... or weighted matches. CONTAINSTABLE is used in the FROM clause of a Transact-SQL SELECT statement and is referenced as if it were a regular table name....
Top answer 1 of 15
1556
Rather slow, but working method to include any of words:
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
OR column1 LIKE '%word2%'
OR column1 LIKE '%word3%'
If you need all words to be present, use this:
SELECT * FROM mytable
WHERE column1 LIKE '%word1%'
AND column1 LIKE '%word2%'
AND column1 LIKE '%word3%'
If you want something faster, you need to look into full text search, and this is very specific for each database type.
2 of 15
157
Note that if you use LIKE to determine if a string is a substring of another string, you must escape the pattern matching characters in your search string.
If your SQL dialect supports CHARINDEX, it's a lot easier to use it instead:
SELECT * FROM MyTable
WHERE CHARINDEX('word1', Column1) > 0
AND CHARINDEX('word2', Column1) > 0
AND CHARINDEX('word3', Column1) > 0
Also, please keep in mind that this and the method in the accepted answer only cover substring matching rather than word matching. So, for example, the string 'word1word2word3' would still match.