Wildcards in SQL
Introduction
SQL wildcards are special pattern characters used mainly with LIKE and NOT LIKE when the exact text is not known. They can describe a known prefix, suffix, substring, fixed character position, or approximate code shape without listing every possible value.
SELECT customer_id, customer_name
FROM customers
WHERE customer_name LIKE 'A%';
This query searches for names beginning with A. The portable wildcard core is small: percent represents zero or more characters, and underscore represents exactly one character. Correct wildcard search also requires deliberate case rules, literal-character escaping, secure parameters, authorization boundaries, sensible result limits, and realistic performance expectations.
What Is a Wildcard?
A wildcard stands for unknown text inside a pattern. If a name is known to begin with John but the remainder is unknown, the fixed text and wildcard can be combined:
WHERE customer_name LIKE 'John%'
John is literal text; percent represents the unknown remainder. The complete pattern is evaluated against each candidate string according to the database's LIKE and collation rules.
Why Wildcards Are Used
Wildcards support requirements such as begins with, ends with, contains, has an unknown character at one position, has an exact length, or follows a partially known code structure. They are useful for autocomplete, administrative filtering, filenames, normalized product codes, and small text-search features.
They are not a replacement for proper data types, regular expressions, or full-text search. The requirement should determine whether a simple SQL pattern is the right tool.
Portable SQL Wildcards
The two standard LIKE wildcards are:
%matches zero or more characters._matches exactly one character.
These are the most portable wildcard concepts across SQL database products. Other symbols such as an asterisk, question mark, or bracket expression may have meanings in shells, file systems, or specific databases, but they are not interchangeable with the standard SQL wildcard pair.
The Percent Wildcard
Percent can consume no text, one character, or many characters.
WHERE topic_name LIKE 'SQL%'
This can match SQL, SQL Basics, SQL Server, and SQL2026. The fact that zero characters are allowed is important: a value exactly equal to the fixed prefix also qualifies.
Prefix, Suffix, and Contains Patterns
-- Starts with Pro.
WHERE product_name LIKE 'Pro%'
-- Ends with .pdf.
WHERE file_name LIKE '%.pdf'
-- Contains cloud anywhere.
WHERE description LIKE '%cloud%'
Wildcard position defines the search. Prefix patterns retain a known beginning. Suffix patterns begin with an unknown sequence. Contains patterns allow unknown sequences on both sides.
The Underscore Wildcard
Underscore represents one and only one character.
-- A followed by exactly one character.
WHERE code LIKE 'A_'
-- Exactly five characters.
WHERE short_code LIKE '_____'
-- AB plus exactly three more characters.
WHERE short_code LIKE 'AB___'
A missing or additional character causes the fixed-length pattern to fail. This makes underscore suitable for simple character-position and length rules.
Percent versus Underscore
'A%' matches A, AB, Alice, and any other value beginning with A. 'A_' matches only two-character values beginning with A. Multiple underscores reserve multiple exact positions.
% = zero or more characters
_ = exactly one character
Tests should include zero, one, and several characters for percent, and zero, exactly one, and two characters for underscore.
Combining Percent and Underscore
A pattern may use both wildcard types:
WHERE product_code LIKE 'AB_%'
WHERE reference_no LIKE 'INV-____-%'
The first requires AB, one additional character, and then any remaining suffix. The second requires INV-, exactly four characters, a hyphen, and any suffix. Write example matches and nonmatches before deploying a complex pattern.
Wildcard Order Matters
Patterns are matched in sequence. '%red%blue%' requires red to appear before blue, though any text may appear around and between them. It does not match values where blue appears first.
-- Both terms, specifically in this order.
WHERE description LIKE '%red%blue%'
-- Either order requires two alternatives.
WHERE description LIKE '%red%blue%'
OR description LIKE '%blue%red%'
For sophisticated term ordering or language-aware search, a full-text system is more suitable.
Wildcards Require a Pattern Operator
Percent and underscore receive wildcard meaning when interpreted by LIKE or a related product-specific pattern operator. Equality treats them as ordinary characters.
name LIKE 'Ann%' -- Pattern search.
name = 'Ann%' -- Exact text Ann% under ordinary equality.
IN also uses equality semantics, so name IN ('Ann%', 'Bob%') does not perform two prefix searches. Use separate LIKE predicates joined with OR.
Wildcards Are Not Ranges
A wildcard pattern describes characters, not a numeric or temporal interval. Do not convert a number or date to text merely to search its display format.
-- Correct numeric range.
WHERE amount BETWEEN :minimum_amount AND :maximum_amount
-- Correct timestamp period.
WHERE created_at >= :period_start
AND created_at < :period_end
Semantic data types and operators are more accurate, portable, and index-friendly than text-pattern workarounds.
Wildcards and NULL
Percent can match an empty string, but it does not match NULL. NULL represents missing or unknown information, so any ordinary LIKE comparison against it returns UNKNOWN.
-- Include missing values deliberately.
WHERE nickname LIKE 'A%'
OR nickname IS NULL
-- Require a known value that does not match.
WHERE nickname IS NOT NULL
AND nickname NOT LIKE 'A%'
NOT LIKE does not automatically include NULL. State the missing-value policy explicitly.
Case, Accent, and Collation Rules
Wildcard characters define pattern shape, but collation controls character comparison. Depending on the database and collation, 'a%' may or may not match Alice. Accented and unaccented characters may compare as equal or different.
PostgreSQL commonly distinguishes LIKE from its case-insensitive ILIKE extension. SQL Server and MySQL often derive behavior from collations. Test the actual deployment configuration rather than assuming universal behavior.
Case-Insensitive Wildcard Search
A common approach applies a case-normalizing function:
WHERE LOWER(customer_name) LIKE LOWER(:pattern)
This can prevent an ordinary index from helping unless the database has a matching functional or expression index. Case-insensitive collations, specialized types, or a normalized indexed column may be better. Do not normalize case-sensitive identifiers, credentials, or security tokens simply for search convenience.
Literal Percent and Underscore
Real data can contain percent and underscore characters. To search for them literally, choose an escape character and use the target database's ESCAPE support.
-- Contains a literal percent sign.
WHERE label LIKE '%!%%' ESCAPE '!'
-- Begins with the literal text file_.
WHERE file_name LIKE 'file!_%' ESCAPE '!'
The escaped wildcard is literal; unescaped wildcard characters retain their pattern meaning. Escape behavior can vary, so test the exact database dialect.
Escaping User Search Text
For a normal literal search box, user-entered percent and underscore characters should usually be treated as data. Escape the escape character first, then percent and underscore, and finally add application-controlled wildcards.
raw term: 50%_off
escaped term: 50!%!_off
contains value: %50!%!_off%
predicate: description LIKE :pattern ESCAPE '!'
If an advanced interface intentionally supports wildcard syntax, label that mode and validate its complexity. Do not make ordinary users guess whether punctuation changes query semantics.
Parameters and Pattern Injection
Bind the completed pattern as a parameter:
WHERE product_name LIKE :search_pattern ESCAPE '!'
Parameterization protects SQL syntax from injection. It does not disable percent or underscore inside the parameter. Wildcard escaping controls pattern semantics, so secure literal search requires both parameter binding and escaping.
Even intentional wildcard search needs authorization, length limits, timeouts, and result limits. A syntactically safe match-all request can still expose too much data or consume excessive resources.
Controlled Search Modes
Applications should define search modes rather than accepting arbitrary SQL fragments:
exact: term
prefix: term%
suffix: %term
contains: %term%
Escape the literal term before adding mode-controlled wildcards. Validate the selected mode server-side and document blank-input behavior. An empty contains term becomes '%%', which matches every non-NULL string.
Prefix Search Performance
A prefix pattern has a known starting value. With compatible types, collation, and indexing, the optimizer may seek to the prefix range rather than inspect the entire index.
WHERE tenant_id = :tenant_id
AND normalized_name LIKE :escaped_prefix
A composite index beginning with tenant_id and followed by normalized_name may support this equality-plus-prefix pattern. Actual benefit depends on selectivity, result size, selected columns, statistics, and database capabilities.
Leading Wildcards and Contains Search
A suffix or contains pattern begins with percent, so the database does not know where the first literal characters occur. A conventional ordered index usually cannot seek directly to an arbitrary internal substring and may scan many values.
Leading wildcards are valid when their cost is acceptable. They may be reasonable for small tables, limited tenant data, infrequent administrative queries, or strict result caps. They become risky when applied by default to large production tables.
Wildcards versus Full-Text Search
Wildcard matching compares character sequences. It does not provide tokenization, stemming, typo tolerance, relevance ranking, phrase search, or language-aware word boundaries.
Use database full-text indexing or a dedicated search service when users expect search-engine behavior across large natural-language content. Wildcards remain useful for short structured text, prefixes, codes, and simple administrative filters.
Wildcards Are Not Regular Expressions
Standard LIKE patterns are intentionally limited. Symbols such as dot, plus, braces, and question mark do not have regular-expression meaning. A question mark is not the portable one-character SQL wildcard; underscore is.
Some products provide regular-expression operators or functions. Those are separate, product-specific features with different performance and escaping rules.
Vendor-Specific Pattern Features
SQL Server can support bracket patterns such as character sets or ranges within LIKE, depending on its dialect and collation:
-- SQL Server-specific examples, not portable SQL.
WHERE code LIKE '[A-C]%'
WHERE code LIKE '[^0-9]%'
Do not move vendor-specific syntax into portable application code without recording the dependency. Percent and underscore are the safest shared core.
Normalization for Searchable Values
Phone numbers, emails, SKUs, postal codes, and account references often have multiple display formats. A normalized search column can remove punctuation or apply an agreed case rule while preserving the original value for display.
WHERE organization_id = :organization_id
AND normalized_phone LIKE :digits_prefix
Normalization must be deterministic and consistent at write and query time. Avoid over-normalizing values where punctuation, accents, or case carry business or security meaning.
Practical Name and Product Patterns
-- First letter A.
WHERE customer_name LIKE 'A%'
-- Last letter a.
WHERE customer_name LIKE '%a'
-- Character n in position two.
WHERE customer_name LIKE '_n%'
-- Product prefix plus exactly four following characters.
WHERE product_code LIKE 'PRD-____'
These patterns are simple enough to explain and test. When a rule grows into several optional segments and complex alternatives, consider structured columns or a regular-expression feature if appropriate.
Email, File, URL, and SKU Patterns
A domain suffix pattern can find email addresses, but frequent domain filtering is usually better served by a parsed domain column and equality. File-extension patterns must account for case rules. URL searches must escape literal percent characters used in encoding. SKU prefix searches work well when the prefix is a stable business convention.
WHERE normalized_email LIKE :domain_suffix ESCAPE '!'
WHERE file_name LIKE '%.pdf'
WHERE normalized_sku LIKE :sku_prefix ESCAPE '!'
LIKE can search these fields, but it should not be mistaken for full syntax validation.
Multiple Terms and Multiple Columns
Decide whether every search term is required or any term is sufficient. AND requires all term patterns; OR accepts any. A multi-column search needs grouping around its alternatives.
WHERE tenant_id = :tenant_id
AND deleted_at IS NULL
AND (normalized_name LIKE :pattern ESCAPE '!'
OR normalized_email LIKE :pattern ESCAPE '!'
OR customer_code LIKE :pattern ESCAPE '!')
Without parentheses, an OR branch can bypass tenant or soft-delete scope. Search predicates are part of the authorization boundary.
Substring Is Not Whole-Word Search
'%art%' matches art, cart, article, and partial. Wildcards do not understand words, punctuation, or linguistic boundaries. Adding spaces around a term fails at the start and end of values and beside punctuation.
If the requirement says whole word, phrase, or relevance, use a feature that models those concepts. Name a substring search accurately in the interface and test plan.
Postal Code and Reference Number Patterns
Wildcards can describe simple reference shapes when the data is genuinely stored as text. A postal or account code may have a known prefix and fixed number of following characters.
-- Five-character codes beginning with 60.
WHERE postal_code LIKE '60___'
-- Reference prefix, four unknown characters, then a fixed suffix.
WHERE reference_no LIKE 'PAY-____-US'
These patterns verify length and fixed positions, but they do not prove that underscores matched digits. Standard LIKE has no portable digit-only wildcard. Use a stronger CHECK constraint, parsed columns, application validation, or a product-specific regular-expression feature when character classes are required.
Keep leading zeros by storing identifiers such as postal codes as character data when arithmetic is not meaningful. That is different from converting true numeric measures to text for wildcard searching.
Pattern Priority and Classification
When several patterns can match one value, CASE uses the first matching branch. Put specific patterns before broad patterns and document overlaps.
CASE
WHEN sku LIKE 'ELEC-PRO-%' THEN 'Professional electronics'
WHEN sku LIKE 'ELEC-%' THEN 'Electronics'
WHEN sku LIKE 'HOME-%' THEN 'Home'
ELSE 'Other'
END
If the broad electronics branch appeared first, the professional branch would never be reached. Test values matching one pattern, several patterns, and no pattern. For frequently changing classifications, a normalized relationship or rules table may be easier to govern than scattered CASE expressions.
Autocomplete with Prefix Wildcards
Autocomplete usually benefits from a normalized prefix pattern because users type from the beginning of a name or code. Require a useful minimum length, limit results, and return a deterministic order.
SELECT product_id, product_name
FROM products
WHERE store_id = :store_id
AND normalized_name LIKE :escaped_prefix ESCAPE '!'
AND is_active = 1
ORDER BY popularity_score DESC, normalized_name, product_id
FETCH FIRST 20 ROWS ONLY;
The application should debounce rapid keystrokes and cancel obsolete requests. The database query should remain scoped even when the prefix is short. Ranking by popularity can improve usefulness, but the final unique tiebreaker prevents unstable ordering.
Suffix Search and Reversed Values
Some workflows search last characters, such as file extensions, account suffixes, or the last four characters of a reference. A leading-percent suffix pattern may scan extensively. For a frequent, important suffix search, a deliberately stored reversed value can turn the suffix into an indexable prefix in products that support the design.
original value: ABCD-1234
reversed value: 4321-DCBA
-- Search original suffix 1234 through a reversed prefix.
WHERE reversed_reference LIKE '4321%'
This strategy adds storage, write maintenance, migration, and explanation costs. A dedicated suffix column may be simpler when only a fixed final segment matters. Never expose partial sensitive identifiers more broadly merely because suffix search is technically possible.
Wildcard Search API Contracts
An API accepting search text should document search mode, case and accent behavior, wildcard policy, minimum and maximum length, result limit, ordering, pagination, and how blank input is handled. It should also state whether percent and underscore are literal or available to advanced callers.
Normalize and escape input once in a trusted service boundary, then bind the final pattern. Do not ask each repository method to invent its own rules. Return validation errors for unsupported modes and excessive terms rather than silently changing the request.
GET /products?query=pro&mode=prefix&limit=20
SQL pattern parameter: pro%
SQL predicate: normalized_name LIKE :pattern ESCAPE '!'
The server derives the wildcard placement. The client controls data and a named mode, not SQL syntax. Authorization and tenant scope must still be independent of the requested pattern.
Advanced Search versus Literal Search
A literal search treats every user character as data. An advanced wildcard search intentionally recognizes percent and underscore. These should be separate, visible modes because they have different usability, performance, and testing requirements.
In advanced mode, still limit pattern length and repeated wildcards, reject unsupported escape sequences, and apply result controls. In literal mode, escape all wildcard characters consistently. Switching modes should not alter authorization scope or expose hidden fields.
Wildcard Search and Sensitive Data
Broad pattern search over email, phone, customer identity, access logs, or tokens can become a data-discovery mechanism. Restrict searchable fields by role, mask returned values, enforce organization boundaries, and log access according to policy.
Passwords, secrets, reset tokens, API keys, and authentication material should not be searchable with LIKE. Store credentials using appropriate one-way hashing or encryption designs and expose only narrowly defined verification operations.
Optional Pattern Filters
Optional search parameters need explicit query construction. A missing parameter may mean omit the predicate, while an empty submitted value may be invalid. Treating both as percent can cause unexpected full-list queries.
Trusted dynamic SQL can include or omit fixed predicate fragments while continuing to bind all values. This can be clearer and easier to optimize than one expression containing several nullable OR conditions. Dynamic SQL is not inherently unsafe; placing untrusted text into the SQL structure is unsafe.
Troubleshooting Unexpected Wildcard Matches
When a row unexpectedly matches or fails, inspect the exact stored value, data type, character length, collation, leading and trailing spaces, Unicode representation, and final bound pattern. A UI may hide spaces or normalize text visually.
Test the expression progressively: equality, LIKE without wildcards, then the intended wildcard form. Verify that the escape character reached the database unchanged and that the driver bound a character type compatible with the column.
SELECT customer_name,
CHAR_LENGTH(customer_name) AS stored_length
FROM customers
WHERE customer_id = :diagnostic_id;
Length-function names vary by database. The goal is to reveal hidden data details before changing a logically correct pattern.
Monitoring Wildcard Queries
Useful production telemetry includes search mode, normalized term length, leading-wildcard presence, result count, execution duration, timeout, cancellation, and pages requested. Aggregate these signals without logging confidential raw terms unless policy permits it.
Track selective prefixes separately from contains searches. A rise in match-all patterns or rows examined may indicate a UI defect, automated abuse, or a new workload that needs full-text indexing. Monitoring turns wildcard performance from guesswork into an observable product decision.
Reviewing Wildcards in Query Plans
In an execution plan, confirm whether a prefix predicate became an index range, whether another equality filter reduced the search first, and how many rows were estimated versus actually read. A plan may use an index yet still read too much data when the prefix is common.
For contains patterns, determine whether the scan is acceptable under realistic concurrency. Review memory grants, CPU, storage reads, and sorting for pagination. The correct improvement may be a narrower product requirement, normalized search field, different index, result cap, or specialized search technology.
Wildcards in UPDATE and DELETE
Pattern-based mutations can affect many rows. Preview the identical WHERE clause with SELECT, inspect examples, count matches, and confirm the expected maximum before changing data.
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 '!';
Here the underscore is escaped because it is literal. Use transactions, affected-row checks, audit records, and recovery procedures. Reject blank or wildcard-only destructive filters.
Wildcards in CASE, CHECK, and JOIN
CASE can classify simple prefixes, while CHECK can enforce a rough character shape:
CASE
WHEN sku LIKE 'ELEC-%' THEN 'Electronics'
WHEN sku LIKE 'HOME-%' THEN 'Home'
ELSE 'Other'
END
LIKE-based CHECK constraints are weak for complex validation such as email or URLs. Pattern joins are also potentially expensive because many row pairs may be evaluated. In an outer join, moving a right-table pattern from ON to WHERE can eliminate unmatched rows and change semantics.
Aggregation, Sorting, and Pagination
A wildcard predicate in WHERE filters rows before GROUP BY. HAVING applies after grouping and should be used only when the grouped result requires pattern filtering. DISTINCT removes duplicate projected values; wildcards do not.
Pattern order does not control result order. Add ORDER BY and a unique tiebreaker for deterministic pagination.
WHERE normalized_name LIKE :prefix_pattern ESCAPE '!'
ORDER BY normalized_name, customer_id
FETCH FIRST 50 ROWS ONLY;
Empty Search and Resource Controls
Define whether an empty search means no filter, no results, or a validation error. Do not let blank input silently become percent-percent and return every visible row.
Apply a minimum useful term length for contains search, a maximum input length, result and page limits, request timeouts, and rate limits. These controls protect the database and reduce accidental data exposure even when SQL injection is impossible.
Unicode, CHAR, and Trailing Spaces
Underscore represents one character according to the database's character semantics, which can interact with Unicode encoding and collation. Test representative languages and equivalent Unicode forms. Accent and case behavior must match the chosen collation.
Fixed-length CHAR values may be padded, and products differ in trailing-space comparison details. Prefer variable-length types for naturally variable text, and test leading spaces, trailing spaces, empty strings, and maximum-length values.
Execution Plans and Selectivity
Inspect actual execution plans for selective prefixes, broad prefixes, suffixes, contains terms, and empty patterns. Review estimated and actual rows, reads, CPU, spills, and whether the expected index is used.
Parameter-sensitive plans can behave differently as pattern shape changes. Keep statistics current and test production-like data distributions. Do not claim that wildcard queries always scan or always seek; the answer depends on pattern, collation, expression, index, and optimizer.
Testing Percent Patterns
Test percent matching zero, one, and many characters. For a prefix, include the exact prefix, a longer match, a case variant, a nonmatch, an empty string, and NULL. For contains search, place the term at the beginning, middle, end, multiple times, and nowhere.
Also test literal percent input and the selected escape character. Verify that ordinary user search treats them literally while an advanced wildcard mode preserves documented pattern behavior.
Testing Underscore Patterns
For each underscore position, test exactly one character, no character, and two characters. Include literal underscores, Unicode text, spaces, punctuation, and maximum-length values. A fixed-length pattern should have cases immediately below, at, and above the required length.
Testing Security and Performance
Security tests should include quotes, comment markers, wildcard-only terms, very long values, unauthorized rows that otherwise match, missing tenant scope, OR-precedence cases, and sensitive fields. Confirm both parameter binding and literal-wildcard escaping.
Performance tests should use realistic table sizes, concurrency, cold and warm caches, narrow and broad patterns, cancellation, result limits, and pagination. A fast one-row developer table does not prove production readiness.
Common Wildcard Mistakes
- Confusing percent and underscore.
- Using an asterisk or question mark as though SQL LIKE were a file system.
- Expecting regular-expression syntax in standard LIKE.
- Forgetting that percent can match zero characters.
- Forgetting that a stored underscore is special in a pattern.
- Putting wildcard patterns inside equality or IN.
- Expecting percent to match NULL.
- Assuming universal case and accent behavior.
- Adding a leading percent to every search.
- Searching numbers and dates as formatted strings.
- Binding a parameter but not escaping literal user wildcards.
- Omitting authorization scope or result limits.
Production Review Checklist
- Confirm that pattern matching is the correct operator family.
- Use percent for zero or more characters and underscore for exactly one.
- Write representative matches and nonmatches for complex patterns.
- Define case, accent, Unicode, whitespace, and NULL behavior.
- Escape percent, underscore, and the escape character for literal search.
- Bind completed patterns as parameters.
- Keep tenant, ownership, visibility, and soft-delete scope mandatory.
- Define empty-input behavior and reject dangerous match-all mutations.
- Prefer selective prefixes when they satisfy the product requirement.
- Use full-text search for linguistic or large-document needs.
- Apply result limits, timeouts, pagination, and rate limits.
- Test actual plans with representative patterns and data volumes.
Interview Questions and Answers
What are SQL wildcards? Special pattern characters interpreted mainly by LIKE and NOT LIKE.
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 exactly five-character values? Use five underscores.
Can an asterisk replace percent? Not in standard SQL LIKE.
Do wildcards work with equality or IN? No; those normally treat wildcard symbols as literal characters.
Does percent match NULL? No. Use IS NULL explicitly.
How do you match literal percent or underscore? Escape it according to the target dialect, commonly with an ESCAPE clause.
Why can percent-term-percent be slow? The leading wildcard removes a known index starting point.
Are wildcards case-sensitive? Behavior depends on the product, type, and collation.
Parameterization versus escaping? Parameterization protects SQL syntax; escaping controls wildcard meaning.
Key Takeaway
SQL wildcards provide a compact vocabulary for simple text patterns. Percent represents zero or more characters, underscore represents exactly one, and their position determines the required shape.
RELIABLE WILDCARDS = CORRECT PATTERN SHAPE
+ DOCUMENTED COLLATION
+ LITERAL CHARACTER ESCAPING
+ BOUND PARAMETERS
+ MANDATORY AUTHORIZATION
+ PERFORMANCE CONTROLS
+ COMPLETE PATTERN TESTS
Use portable wildcards for straightforward character patterns, semantic operators for typed data, and specialized search technology when users need whole words, ranking, linguistic analysis, or scalable arbitrary substring discovery.