SQL Contains: LIKE vs CONTAINS vs POSITION by Database
TL;DR: To do a "contains" check in SQL, use WHERE column LIKE '%text%'; it works in every database. CONTAINS() is a SQL Server full-text predicate (and a plain substring function in Snowflake), so it fails on most tables. If you would rather ask "which customers have gmail in their email?" and get the query written, run and shown for you, AI for Database does exactly that on your own database.
How to do contains in SQL comes down to one portable answer and a few dialect-specific ones. The portable answer is the LIKE operator with a wildcard on each side: WHERE name LIKE '%laptop%' returns every row whose name has "laptop" anywhere in it. There is no CONTAINS function in standard SQL. SQL Server has a CONTAINS predicate, but it is a full-text search feature that only runs on columns with a full-text index (Microsoft Learn). Snowflake has a CONTAINS(expr1, expr2) function that is a simple substring test (Snowflake docs). PostgreSQL, MySQL and SQLite use LIKE, plus position functions such as strpos, INSTR and LOCATE. Below: one table with every method, which ones can use an index, and the cases where each returns matches you did not want.
Try AI for Database free at aifordatabase.com (sign up, sample data included). Ask in plain English, and every answer shows the SQL it ran.
Every way to do contains in SQL, compared
| Method | AI for Database | LIKE '%x%' | CONTAINS() (SQL Server) | POSITION / STRPOS / INSTR | ILIKE / regex |
|---|---|---|---|---|---|
| Works in | PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Supabase and more | Every SQL database | SQL Server, Azure SQL | Most (names differ) | PostgreSQL (ILIKE, ~*), MySQL REGEXP |
| Needs setup | Connect the database once | None | Full-text catalog and index | None | None |
| Matches | Writes the right method for your dialect | Any substring | Whole words, prefixes, inflections | Any substring | Substring, pattern |
| Case sensitivity | Shown in the generated SQL | Follows collation | Follows collation | Follows collation | ILIKE and ~* ignore case |
| Can use an index | Uses whatever the SQL can | Only with pg_trgm (Postgres) or left-anchored 'x%' | Yes, the full-text index | No | pg_trgm in Postgres |
| Wrong-match risk | You check the shown SQL | % and _ in the search text act as wildcards | Misses substrings inside words | Low | Regex metacharacters |
| Best for | People who don't want to write the query | Default choice | Word search over large text | Getting the position too | Case-insensitive or pattern search |
Behaviour checked against PostgreSQL, MySQL 8.4, SQL Server, Snowflake, BigQuery and SQLite documentation, read November 19, 2026.
Doing contains in SQL with AI for Database
AI for Database connects to PostgreSQL, MySQL, SQL Server, SQLite, BigQuery, Supabase and other sources, and turns a plain-English question into SQL for that dialect. Ask "show customers whose email contains gmail" and it writes the query, runs it read-only, and returns the table with the SQL beside it, so you can see whether it used LIKE, ILIKE or something else. You can then pin the answer to a self-refreshing dashboard, or set a workflow such as "when a support ticket subject contains refund, send a Slack alert". It does not create full-text indexes for you; index design stays with whoever owns the database.
Is there a CONTAINS function in SQL?
Not in standard SQL. CONTAINS exists in two well-known places, and they mean different things:
- SQL Server / Azure SQL:
CONTAINS(column, 'search condition')is a full-text predicate. It supports prefix terms ("lap*"), proximity (NEAR), inflections (FORMSOF(INFLECTIONAL, drive)also finds drove, driving), thesaurus terms andAND/AND NOT/OR(CONTAINS reference). The column must be in a full-text index; otherwise SQL Server raises error 7601, "Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ... because it is not full-text indexed." - Snowflake:
CONTAINS(expr1, expr2)"returns true if expr1 contains expr2". It is a plain, case-sensitive substring test with no index setup (Snowflake CONTAINS).
BigQuery's closest equivalent is CONTAINS_SUBSTR, which is case-insensitive: CONTAINS_SUBSTR('the blue house', 'Blue house') returns TRUE (BigQuery string functions).
-- SQL Server full-text (needs a full-text index on Name, Description)
SELECT Id, Name FROM Product WHERE CONTAINS((Name, Description), '"lap*"');
-- Snowflake
SELECT id, name FROM product WHERE CONTAINS(name, 'Laptop');
-- BigQuery
SELECT id, name FROM product WHERE CONTAINS_SUBSTR(name, 'laptop');What is the difference between LIKE and CONTAINS?
LIKE matches characters; SQL Server CONTAINS matches words. name LIKE '%cat%' returns "cat", "category" and "concatenate", because it looks for the three letters anywhere. CONTAINS(name, 'cat') returns only rows with the word "cat", because the full-text engine splits text into words first. That makes CONTAINS better for searching product descriptions or ticket bodies, and worse when you really want any substring (part numbers, email domains, URL fragments).
The other difference is speed. LIKE '%x%' with a leading wildcard cannot use a normal B-tree index, so on a big table it scans every row. CONTAINS reads the full-text index.

How do I check if a string contains a substring in Postgres or MySQL?
Use LIKE with % on both sides in either database. Each also has a position function that returns 0 when the substring is missing.
PostgreSQL
-- case-sensitive
SELECT * FROM customers WHERE email LIKE '%gmail%';
-- case-insensitive (PostgreSQL extension)
SELECT * FROM customers WHERE email ILIKE '%gmail%';
-- position function: 0 means not found
SELECT * FROM customers WHERE strpos(email, 'gmail') > 0;
SELECT * FROM customers WHERE position('gmail' IN email) > 0;strpos('high', 'ig') returns 2 (PostgreSQL string functions). ILIKE and the regex operator ~* ignore case (pattern matching).
MySQL
SELECT * FROM customers WHERE email LIKE '%gmail%';
-- position functions: note the opposite argument order
SELECT * FROM customers WHERE INSTR(email, 'gmail') > 0;
SELECT * FROM customers WHERE LOCATE('gmail', email) > 0;In MySQL, LIKE follows the column's collation, and the default utf8mb4_0900_ai_ci is case-insensitive, so '%Gmail%' matches "gmail" (MySQL case sensitivity). For word search over large text, MySQL's version of full-text is MATCH(col) AGAINST('term'), which needs a FULLTEXT index (MySQL full-text search).
SQLite uses the same LIKE, which is case-insensitive for ASCII letters by default, and instr(X, Y) for the position (SQLite expressions).
Which contains method can use an index?
Only three of them:
- SQL Server `CONTAINS` and MySQL `MATCH ... AGAINST` read their full-text index.
- PostgreSQL `LIKE '%x%'` and `ILIKE` can use a trigram index from the
pg_trgmextension. The PostgreSQL docs state that pg_trgm GIN and GiST indexes supportLIKE,ILIKE,~and~*, and that "the search string need not be left-anchored" (pg_trgm). - Left-anchored `LIKE 'x%'` (starts with) can use a plain B-tree index.
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX customers_email_trgm ON customers USING gin (email gin_trgm_ops);
-- now this can use the index instead of scanning the table
EXPLAIN SELECT * FROM customers WHERE email ILIKE '%gmail%';One catch from the same docs: a pattern with no extractable trigrams (for example a search string of one or two characters) "will degenerate to a full-index scan". strpos, INSTR, LOCATE and CHARINDEX wrap the column in a function, so they never use these indexes.

When does LIKE return wrong matches?
LIKE treats % and _ in your search text as wildcards. Searching for "50%" with LIKE '%50%%' matches "500" and "1500", and searching for "user_id" matches "userXid". Escape them:
-- PostgreSQL / MySQL / SQLite: match a literal 50%
SELECT * FROM promos WHERE label LIKE '%50\%%' ESCAPE '\';
-- SQL Server: square brackets also work
SELECT * FROM promos WHERE label LIKE '%50[%]%';The second trap is case. The same LIKE '%Gmail%' returns rows in MySQL and SQLite and none in PostgreSQL, because PostgreSQL LIKE is case-sensitive. Use ILIKE there, or LOWER(col) LIKE '%gmail%' when you need portable SQL. The third is NULL: NULL LIKE '%x%' is NULL, not FALSE, so NOT LIKE silently drops NULL rows too.
Choose LIKE if / choose CONTAINS if
Choose LIKE if you want any substring, your table is small or medium, or you need SQL that runs on every database. In PostgreSQL, add a pg_trgm index when the table grows.
Choose CONTAINS (or MATCH AGAINST) if you search words in long text such as descriptions, notes or ticket bodies, on SQL Server or MySQL, and you can create a full-text index.
Choose a position function if you also need where the substring starts, for example to cut out the domain of an email.
Choose AI for Database if the people asking these questions don't write SQL. They ask in plain English, see the generated SQL, and keep the answer on a dashboard. More syntax lives in our SQL cheat sheet, the guide to filtering in SQL, how to read a PostgreSQL EXPLAIN plan and querying PostgreSQL without SQL.
Try AI for Database free at aifordatabase.com: start with sample data.
Frequently Asked Questions
Is there a CONTAINS function in SQL?
Not in standard SQL. SQL Server has a CONTAINS full-text predicate that needs a full-text index, and Snowflake has a CONTAINS substring function. Everywhere else, use LIKE '%text%'.
What is the difference between LIKE and CONTAINS?
LIKE matches characters anywhere in the value and usually scans the table. SQL Server CONTAINS matches whole words, prefixes and word forms through a full-text index.
How do I check if a string contains a substring in Postgres or MySQL?
WHERE col LIKE '%text%' in both. PostgreSQL also has ILIKE for case-insensitive search and strpos(col, 'text') > 0; MySQL has INSTR(col, 'text') > 0 and LOCATE('text', col) > 0.
Which contains method can use an index?
SQL Server CONTAINS and MySQL MATCH AGAINST use full-text indexes, PostgreSQL LIKE and ILIKE can use a pg_trgm GIN or GiST index, and LIKE 'text%' can use a B-tree index. Position functions cannot.
How do I do a case-insensitive contains in SQL?
In PostgreSQL use ILIKE. In MySQL and SQL Server, LIKE is case-insensitive under the default collations. For portable SQL, compare LOWER(col) LIKE '%text%'.
Can I search several words at once?
Yes. Chain conditions (col LIKE '%a%' OR col LIKE '%b%'), or in SQL Server use CONTAINS(col, 'a OR b').