LIKE Operator in SQL
Introduction
The SQL LIKE operator searches character data using a pattern instead of requiring one exact value. It supports requirements such as names beginning with John, email addresses ending in a company domain, product codes with a known prefix, or descriptions containing a short search term.
SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'John%';
This pattern can match John, Johnny, Johnson, and John Smith. LIKE combines literal characters with wildcards. The percent sign represents zero or more characters, while the underscore represents exactly one character. Reliable search also requires deliberate case and accent rules, correct wildcard escaping, secure parameter binding, authorization scope, and performance controls.
Why LIKE Exists
The equality operator normally asks for one exact comparable value:
WHERE customer_name = 'John'
Applications often need looser text matching: begins with John, ends with Smith, contains test, or has one unknown character in a fixed position. LIKE expresses these simple patterns without requiring a full-text engine or regular expression feature.
Basic LIKE Syntax
expression LIKE 'pattern'
The expression is usually a character column, and the pattern contains literal text, wildcard characters, or both. A pattern without wildcards behaves similarly to equality under the applicable LIKE and collation rules, although equality communicates exact matching more clearly.
SELECT employee_id, employee_name
FROM employees
WHERE employee_name LIKE 'A%';
LIKE returns TRUE, FALSE, or UNKNOWN. WHERE keeps only TRUE rows, so NULL values do not match unless they are handled separately.
The Percent Wildcard
The percent wildcard, %, represents zero or more characters. Because zero is allowed, 'A%' matches both A and longer values such as Amy, Adam, and Alice.
-- Begins with John.
WHERE name LIKE 'John%'
-- Ends with Smith.
WHERE name LIKE '%Smith'
-- Contains test anywhere.
WHERE description LIKE '%test%'
Wildcard position changes the meaning. A trailing percent defines a prefix search, a leading percent defines a suffix search, and percents on both sides define a contains search.
The Underscore Wildcard
The underscore, _, represents exactly one character. It does not mean any number of characters.
-- Matches A1, AB, or A- but not A or ABC.
WHERE code LIKE 'A_'
-- Begins with SKU- and has exactly three following characters.
WHERE sku LIKE 'SKU-___'
Combining percent and underscore supports simple shape requirements. For example, 'AB_%' requires AB, one additional character, and then any remaining suffix.
Percent versus Underscore
Choosing the wrong wildcard changes the accepted length. 'J_n' matches Jan, Jen, and Jin but not John. 'J%n' can match Jn, Jan, John, and Johnson because percent can consume any number of characters, including none.
_ = exactly one character
% = zero or more characters
Use explicit test cases for zero characters, one character, and several characters whenever wildcard length matters.
LIKE with AND and OR
AND combines a pattern with mandatory filters:
SELECT product_id, product_name
FROM products
WHERE tenant_id = :tenant_id
AND product_name LIKE :name_pattern
AND deleted_at IS NULL;
OR supports alternative patterns, but it must be grouped when another condition applies to every alternative:
WHERE tenant_id = :tenant_id
AND (customer_name LIKE :pattern
OR email_address LIKE :pattern)
Without parentheses, the email branch could bypass tenant scope due to operator precedence. This is a common search-related authorization defect.
Multiple LIKE Patterns
IN does not interpret wildcards, so multiple patterns require separate LIKE predicates or a database-specific alternative.
WHERE product_name LIKE 'Pro%'
OR product_name LIKE 'Enterprise%'
product_name IN ('Pro%', 'Enterprise%') searches for literal percent characters under ordinary equality semantics. Use IN for exact discrete values and LIKE for patterns.
LIKE versus Equality, IN, and BETWEEN
Equality matches one exact value. IN matches any value from a discrete set. BETWEEN selects an inclusive ordered range. LIKE searches a character pattern. Choosing the operator that matches the requirement makes SQL easier to understand and optimize.
name = 'John' -- exact value
status IN ('OPEN', 'PENDING') -- exact set
price BETWEEN 10 AND 50 -- inclusive range
name LIKE 'John%' -- text pattern
LIKE should not be used to search numeric ranges or dates stored in their proper semantic types. Convert user input to the correct type and use numeric or temporal predicates.
NOT LIKE
NOT LIKE selects known values that do not satisfy a pattern.
SELECT file_name
FROM documents
WHERE file_name NOT LIKE 'temp_%';
It does not include NULL file names because both LIKE and NOT LIKE return UNKNOWN for a NULL expression. Add OR file_name IS NULL only when missing names should be included by the business rule.
LIKE and NULL
NULL represents missing or unknown information. It is not an empty string and cannot be matched by '%'.
-- Include matching values and missing values explicitly.
WHERE nickname LIKE 'A%'
OR nickname IS NULL
-- Require a known nonmatching value.
WHERE nickname IS NOT NULL
AND nickname NOT LIKE 'A%'
Writing the NULL policy visibly prevents later readers from assuming that NOT LIKE automatically includes missing data.
Case Sensitivity and Collation
Whether 'a%' matches Alice depends on the database product, column collation, expression collation, and data type. SQL Server and MySQL commonly derive behavior from configured collations. PostgreSQL LIKE is normally case-sensitive and provides ILIKE as a case-insensitive extension.
Do not promise case-insensitive search merely because it worked in one development database. Define the requirement, configure or select an appropriate collation, and test the deployed environment. Accent sensitivity and Unicode equivalence require the same care.
Case-Insensitive Search Strategies
A common portable-looking technique normalizes both sides:
WHERE LOWER(customer_name) LIKE LOWER(:pattern)
This can prevent ordinary index access unless the database has a matching expression or functional index. Product-specific case-insensitive collations, character types, or ILIKE may be clearer. Another option is to store a normalized searchable value when normalization is a stable business rule.
Do not normalize security identifiers, passwords, tokens, or case-sensitive codes casually. Search convenience must not weaken identity semantics.
Searching for Literal Percent and Underscore
When percent or underscore should be ordinary text, escape it using a chosen escape character and an ESCAPE clause supported by the target database.
-- Find values containing a literal percent sign.
WHERE discount_label LIKE '%!%%' ESCAPE '!'
-- Find values beginning with the literal text file_.
WHERE file_name LIKE 'file!_%' ESCAPE '!'
The first escaped percent is literal while the surrounding percent signs remain wildcards. The escaped underscore is literal rather than a one-character placeholder. Escape syntax and default behavior can vary, so verify the exact target dialect.
User Input and Pattern Escaping
An application must decide whether user-entered percent and underscore characters are search operators or literal text. For a normal contains box, users generally expect their input to be literal. Escape the selected escape character first, then percent and underscore, and finally add application-controlled wildcards.
raw input: 50%_off
escaped input: 50!%!_off
bound pattern: %50!%!_off%
SQL: column LIKE :pattern ESCAPE '!'
If an advanced interface intentionally supports wildcards, document that mode and still validate length and complexity. Mixing literal and wildcard behavior without explanation leads to surprising result sets.
Parameterization Is Still Required
Bind the completed pattern as a parameter rather than concatenating it into SQL:
WHERE product_name LIKE :search_pattern ESCAPE '!'
Parameterization prevents SQL injection and preserves type information. It does not escape LIKE wildcards. SQL injection and pattern injection are separate concerns: binding protects SQL syntax, while wildcard escaping controls how user text participates in the pattern. A secure implementation normally needs both.
Defining Search Modes
A search API should define whether a term means exact, prefix, suffix, or contains. Build that mode server-side rather than accepting arbitrary SQL patterns.
exact: :term
prefix: :term || '%'
suffix: '%' || :term
contains: '%' || :term || '%'
String-concatenation syntax differs by database, so applications often construct the bound parameter value instead. Validate permitted modes, normalize according to documented rules, and return the applied mode in diagnostic metadata when useful.
Prefix Search and Indexes
A prefix pattern such as 'Sam%' has a known starting boundary. Under compatible collation and index conditions, the optimizer may translate it into an index range and avoid scanning unrelated prefixes.
WHERE normalized_name LIKE :escaped_prefix
An index on normalized_name may help when the prefix is selective and the query returns a limited number of rows. Data distribution, collation, selected columns, statistics, and parameter behavior still influence the plan.
Leading Wildcards and Performance
A leading wildcard such as '%tech' or '%tech%' removes the known beginning of the value. A conventional ordered index usually cannot seek directly to an arbitrary substring, so the database may inspect many rows.
Leading wildcards are not logically wrong. They can be appropriate for small tables, administrative tools, infrequent queries, or tightly scoped data. The design problem is using unbounded contains search on large data while promising interactive response times.
LIKE Is Not a Full-Text Search Engine
LIKE is effective for simple prefixes, suffixes, substrings, and fixed-position patterns. It does not provide language-aware tokenization, stemming, relevance ranking, phrase semantics, typo tolerance, or advanced linguistic analysis.
For large articles, logs, product catalogs, or document collections, evaluate full-text indexes or a dedicated search service. Keep LIKE for fields and workloads where its semantics and cost are appropriate.
Data Normalization Improves Search
Phone numbers, email addresses, SKUs, and business codes often appear in several display formats. Searching every variation with complicated patterns is fragile. Store or derive a normalized searchable representation while retaining the original display value when needed.
WHERE normalized_phone LIKE :digits_prefix
AND tenant_id = :tenant_id
Normalization should be deterministic, documented, and applied consistently at write and query time. Avoid functions on every stored row when a normalized indexed column represents a genuine business need.
Practical Email Search
Email search illustrates why semantics must be defined before writing a pattern. A support agent may need an exact address, every address in a domain, or a prefix suggestion while typing. Those are different queries.
-- Exact address is best expressed with equality.
WHERE normalized_email = :normalized_email
-- Addresses ending in one trusted domain.
WHERE normalized_email LIKE :domain_suffix ESCAPE '!'
-- Type-ahead prefix over the complete normalized address.
WHERE normalized_email LIKE :email_prefix ESCAPE '!'
A suffix pattern normally begins with percent and may not use a conventional ordered index efficiently. If domain search is frequent, store the parsed domain in its own indexed column and compare it with equality. This also avoids false matches such as an address whose local part happens to end with the requested text.
Email addresses have more complex standards than most application validators implement. LIKE can support a search interface, but it should not be treated as authoritative email syntax validation.
Phone Number, SKU, and Code Search
Phone numbers should usually have a normalized digits or international representation for search. A user-entered prefix can then be normalized before binding, while the formatted number remains available for display. Do not use patterns to guess across every possible punctuation format.
SKUs and internal codes often have meaningful prefixes. A pattern such as 'ELEC-%' can work well when the prefix convention is stable and indexed. If each segment has separate business meaning, storing category, family, and sequence as separate attributes may support stronger validation and simpler queries.
SELECT product_id, sku, product_name
FROM products
WHERE tenant_id = :tenant_id
AND normalized_sku LIKE :sku_prefix ESCAPE '!'
ORDER BY normalized_sku, product_id;
Define whether hyphens and spaces are literal, optional, or normalized away. A pattern should implement a documented code rule rather than compensate for inconsistent source data.
File Name, URL, and Log Search
LIKE can find simple file extensions or URL fragments, but these values contain punctuation that may carry meaning. An underscore in a file name is a wildcard unless escaped. A percent-encoded URL can include literal percent characters that also require escaping.
-- Find PDF file names, subject to configured case rules.
WHERE file_name LIKE '%.pdf'
-- Find a literal underscore in a controlled prefix.
WHERE file_name LIKE 'invoice!_%' ESCAPE '!'
Log-message contains search is convenient for a small operational table, yet it scales poorly and may reveal secrets embedded in logs. Mature logging systems usually parse structured fields and use an indexed search platform. Apply retention, access control, redaction, and result limits before exposing log search.
Product and Customer Search Examples
A product catalog may offer a quick prefix over a normalized product name and an exact category filter:
SELECT product_id, product_name, price
FROM products
WHERE store_id = :store_id
AND category_id = :category_id
AND normalized_name LIKE :name_prefix ESCAPE '!'
AND is_active = 1
ORDER BY normalized_name, product_id
FETCH FIRST 25 ROWS ONLY;
A customer support search may check name, email, and customer code. The OR group must remain inside organization and visibility conditions. Limit the projected fields so a broad match does not expose unnecessary personal data.
WHERE organization_id = :organization_id
AND visibility_level <= :viewer_level
AND (normalized_name LIKE :pattern ESCAPE '!'
OR normalized_email LIKE :pattern ESCAPE '!'
OR customer_code LIKE :pattern ESCAPE '!')
Search is an authorization surface, not merely a convenience feature. Every matching branch must obey the same data-access policy.
Multiple Search Terms
When users enter several words, decide whether every term must appear or any term may appear. The difference should be visible in the generated predicate and user interface.
-- Both terms must appear.
WHERE description LIKE :term_one ESCAPE '!'
AND description LIKE :term_two ESCAPE '!'
-- Either term may appear.
WHERE description LIKE :term_one ESCAPE '!'
OR description LIKE :term_two ESCAPE '!'
Tokenizing natural-language input introduces language, punctuation, stop-word, ordering, and ranking questions that LIKE does not solve. For more than a small number of terms, full-text search is usually a better model. If LIKE remains appropriate, cap term count and group multi-column alternatives carefully so authorization and AND/OR meaning remain correct.
Word Search versus Substring Search
A contains pattern finds character sequences, not words. '%art%' can match art, cart, partial, and article. Adding spaces around the term is unreliable because words may appear beside punctuation or at string boundaries.
If the product promises whole-word matching, stemming, phrases, or relevance, do not label a substring LIKE query as equivalent. Use a text-search feature designed for token boundaries, and document any language-specific behavior. Clear terminology prevents users and testers from reporting correct substring behavior as a defect.
Cross-Database Portability
Percent and underscore form the portable core of LIKE, but surrounding behavior varies. PostgreSQL offers ILIKE for case-insensitive matching. SQL Server may support bracket character patterns in LIKE, while those brackets are not portable standard wildcards. Escape-character defaults and string-concatenation syntax also differ.
Collation rules can change after migration or differ between columns in one database. Unicode character treatment, trailing spaces, and fixed-length CHAR padding may produce results that surprise a team testing only VARCHAR data.
Keep portable application requirements separate from vendor extensions. When a product-specific feature is chosen, record the dependency and include migration tests for every important pattern category.
Trailing Spaces and Fixed-Length Character Data
CHAR columns may be padded to a fixed length, while VARCHAR stores variable-length text. Database products and collations can treat trailing spaces differently during comparisons. A pattern that appears correct in a query tool may behave differently after values are cast, imported, or compared through another type.
Use VARCHAR for naturally variable text unless fixed-width semantics are required. Test empty strings, trailing spaces, leading spaces, and values at the declared maximum length. Trimming every row in the WHERE clause can hide data-quality problems and interfere with index use; normalize at controlled boundaries where possible.
Unicode, Accents, and International Search
International text can have case mappings and equivalent-looking Unicode representations that are more complex than ASCII. Collation determines whether accented letters compare with unaccented letters and whether case variants match. User expectations may also differ by language.
Choose a supported collation based on product requirements, not merely convenience. Test representative scripts, composed and decomposed characters, accents, and locale-specific case behavior. If a separate search engine performs normalization, ensure its behavior is consistent enough with database filters used for authorization or reconciliation.
Troubleshooting Unexpected LIKE Results
When a row unexpectedly matches or fails, inspect the stored value, length, data type, collation, Unicode code points, leading and trailing spaces, and the final bound pattern. Logging only the raw user term is insufficient because application escaping and wildcard placement may have transformed it.
Reduce the query to one expression and test equality, LIKE without wildcards, and the intended pattern separately. Confirm whether NULL is involved. Then inspect execution plans only after result semantics are correct.
SELECT customer_name,
CHAR_LENGTH(customer_name) AS stored_length
FROM customers
WHERE customer_id = :diagnostic_customer_id;
Function names vary by product, but the diagnostic principle is the same: reveal hidden characters and actual types instead of relying on a formatted grid.
Monitoring Search in Production
Useful search telemetry includes search mode, normalized term length, wildcard position, result count, execution duration, timeout or cancellation, and the number of pages requested. Avoid recording raw sensitive terms when aggregate characteristics are sufficient.
Track broad patterns separately from selective prefixes because their resource profiles differ. Alert on repeated match-all attempts, oversized requests, and sudden increases in rows examined. Use telemetry to decide whether a field needs a normalized index, a different interaction design, or migration to full-text search.
Do Not Search Semantic Types as Text
Formatting a date or number into text and then applying LIKE often introduces locale, correctness, and indexing problems.
-- Avoid text formatting for a date period.
WHERE order_date >= :period_start
AND order_date < :period_end
-- Avoid textual numeric prefixes for numeric meaning.
WHERE amount >= :minimum_amount
AND amount <= :maximum_amount
Use LIKE when the stored information is meaningfully character data, not as a shortcut around proper typing.
LIKE in UPDATE and DELETE
Pattern-based mutations can affect far more rows than expected, particularly with an empty term that produces '%%'. Preview with a SELECT using the identical WHERE clause, count rows, inspect samples, and enforce a maximum.
SELECT document_id, file_name
FROM documents
WHERE tenant_id = :tenant_id
AND file_name LIKE 'temp!_%' ESCAPE '!';
DELETE FROM documents
WHERE tenant_id = :tenant_id
AND file_name LIKE 'temp!_%' ESCAPE '!';
Use transactions, audit records, authorization checks, and recovery procedures. Never allow a blank user search term to become an unrestricted destructive wildcard.
LIKE in CASE, CHECK, and JOIN
CASE can classify simple character shapes:
CASE
WHEN sku LIKE 'ELEC-%' THEN 'Electronics'
WHEN sku LIKE 'HOME-%' THEN 'Home'
ELSE 'Other'
END
CHECK with LIKE can enforce a rough format, but it is weak validation for complex domains such as email, URLs, or security identifiers. Prefer precise constraints and application validation where requirements are richer.
LIKE can appear in a JOIN, but pattern joins are often expensive because many pairs must be tested. With outer joins, placing a right-table LIKE condition in WHERE can remove NULL-extended rows and effectively change the join. Keep eligibility predicates in the ON clause when unmatched outer rows must remain.
LIKE with Aggregation and Ordering
Use WHERE LIKE to filter rows before GROUP BY. HAVING is appropriate only when a pattern applies to a grouped expression or alias supported by the product. DISTINCT removes duplicate projected values; LIKE itself does not.
SELECT category_code, COUNT(*) AS matching_products
FROM products
WHERE product_name LIKE :prefix_pattern
GROUP BY category_code
ORDER BY matching_products DESC, category_code;
LIKE does not sort results. Add ORDER BY and a unique tiebreaker when pagination or repeatable exports matter.
Tenant-Safe Multi-Column Search
A common application search checks several columns while enforcing one authorization boundary:
SELECT customer_id, customer_name, email_address
FROM customers
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (customer_name LIKE :pattern ESCAPE '!'
OR email_address LIKE :pattern ESCAPE '!'
OR customer_code LIKE :pattern ESCAPE '!')
ORDER BY customer_name, customer_id;
The parentheses are essential. The pattern must also be escaped according to the documented search mode, limited in length, and bound as a parameter.
Optional Search Parameters
A blank search term should have explicit product behavior. It may mean no filtering, no results, or a validation error. Do not accidentally convert blank input into a match-all pattern.
Static optional-filter expressions can be convenient, but dynamic SQL built with trusted fragments and bound values may produce clearer plans. Dynamic SQL is not inherently unsafe; concatenating untrusted values is unsafe. Keep query structure controlled by server code and data in parameters.
Search Resource Controls
Contains searches can become a resource-abuse path even when injection is impossible. Apply a minimum useful term length, maximum input length, result limit, request timeout, rate limit, and authorization scope appropriate to the product.
Return deterministic pages and avoid revealing sensitive fields through broad pattern searches. Monitor execution time, rows examined, result count, cancellation, and repeated expensive patterns without logging confidential search text unnecessarily.
Pagination for Pattern Results
Stable pagination requires ORDER BY. Offset pagination may become slow or inconsistent for large changing result sets, while keyset pagination continues after the last ordered values.
WHERE tenant_id = :tenant_id
AND normalized_name LIKE :prefix_pattern
AND (normalized_name > :last_name
OR (normalized_name = :last_name AND customer_id > :last_id))
ORDER BY normalized_name, customer_id
FETCH FIRST 50 ROWS ONLY;
The exact syntax varies, but the principle is stable: the pattern defines eligibility, and a deterministic order defines traversal.
Execution Plans and Selectivity
Inspect estimated and actual rows, index access, reads, CPU, spills, and returned row counts using realistic patterns. A highly selective prefix may perform very differently from an empty or one-character prefix. Parameter-sensitive plans can also vary when one prepared statement serves exact, prefix, and contains modes.
Keep statistics current and test production-like data distributions. Avoid declaring that LIKE always uses or never uses an index; pattern shape, collation, expression, index design, and optimizer capabilities determine the outcome.
Testing Wildcard Semantics
For percent, test zero, one, and many matched characters. For underscore, test exactly one character as well as zero and two characters. Test literal percent, literal underscore, the escape character itself, empty strings, NULL, leading and trailing spaces, Unicode text, case variants, and accented forms.
Contains search tests should place the term at the beginning, middle, end, multiple positions, and nowhere. Confirm that an escaped user term is treated literally while an advanced wildcard mode behaves as documented.
Testing Security and Performance
Security tests should include quotes, comment markers, wildcard-only input, very long terms, mixed authorized and unauthorized rows, missing tenant scope, and OR-precedence attempts. Verify that parameterization protects SQL syntax and wildcard escaping protects literal-search semantics.
Performance tests should cover exact, selective prefix, broad prefix, suffix, contains, empty, and minimum-length patterns at realistic table sizes. Measure concurrent use, cancellation, page transitions, and result limits rather than timing one cached query.
Common LIKE Mistakes
- Believing percent means exactly one character.
- Believing underscore means any number of characters.
- Putting wildcard strings inside IN.
- Expecting LIKE or NOT LIKE to match NULL automatically.
- Assuming case and accent behavior without checking collation.
- Adding leading wildcards to every search regardless of scale.
- Searching numeric or temporal data through formatted text.
- Binding parameters but forgetting to escape literal wildcards.
- Omitting authorization scope from a search query.
- Allowing an OR branch to bypass mandatory conditions.
- Treating LIKE as a regular-expression or full-text engine.
- Allowing unlimited, unpaged, match-all searches.
Production Review Checklist
- Define exact, prefix, suffix, or contains semantics.
- Choose percent and underscore according to required length.
- Document case, accent, Unicode, and trailing-space behavior.
- Define NULL and blank-input behavior.
- Escape user wildcards when input should be literal.
- Bind the completed pattern as a parameter.
- Keep authorization predicates outside grouped OR patterns.
- Use semantic data types instead of formatted text searches.
- Prefer prefix search when it satisfies the requirement.
- Evaluate full-text search for large natural-language content.
- Apply minimum length, result limits, timeouts, and rate limits.
- Inspect actual plans using representative pattern shapes.
Interview Questions and Answers
What does LIKE do? It compares character data with a pattern containing literal text and optional wildcards.
What does percent mean? Zero or more characters.
What does underscore mean? Exactly one character.
How do you find names beginning with A? Use name LIKE 'A%'.
How do you find names ending with son? Use name LIKE '%son'.
LIKE versus IN? LIKE searches patterns; IN tests exact membership.
Does LIKE match NULL? No. It returns UNKNOWN; use IS NULL explicitly.
Is LIKE case-sensitive? It depends on the product, data type, and collation.
Why can a leading percent be expensive? It removes the known prefix that a conventional ordered index can seek.
Does parameterization escape wildcards? No. Parameter binding and LIKE escaping solve different problems.
Is LIKE a regular expression? No. Standard LIKE primarily supports percent and underscore.
How do you search for a literal percent? Escape it using a defined escape character and the target dialect's ESCAPE support.
Key Takeaway
LIKE is SQL's standard tool for simple character patterns. Percent handles variable-length text, underscore handles one unknown character, and their placement defines prefix, suffix, contains, or fixed-position search.
RELIABLE LIKE SEARCH = DEFINED SEARCH MODE
+ CORRECT WILDCARDS
+ EXPLICIT COLLATION RULES
+ LITERAL-WILDCARD ESCAPING
+ BOUND PARAMETERS
+ MANDATORY AUTHORIZATION
+ PERFORMANCE LIMITS
+ REPRESENTATIVE TESTS
Use LIKE for straightforward text patterns, semantic operators for typed data, and full-text technology when the product needs linguistic search, relevance, or scalable arbitrary substring discovery.