SQL WILDCARDS

What are SQL wildcards and how do they work?

SQL wildcards are special characters used in pattern-matching predicates like LIKE and SIMILAR TO to flexibly filter text.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL WILDCARDS: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, DB2, Snowflake, Redshift (with minor syntax differences)

SQL WILDCARDS Full Explanation

SQL wildcards let you match unknown or variable portions of a character string when filtering data. In Standard SQL the percent sign (%) matches zero or more characters and the underscore (_) matches exactly one character in a LIKE predicate. Dialects extend this set: square brackets ( [a-z] ) or POSIX character classes in SIMILAR TO, caret (^) or exclamation mark (!) for negation inside brackets, and question mark (?) in some systems for a single character. Wildcards are interpreted only inside pattern-matching operators (LIKE, NOT LIKE, ILIKE, SIMILAR TO, REGEXP_LIKE). Outside these contexts they are ordinary characters.Behavior details:- Case sensitivity depends on the operator and collation. LIKE is case sensitive in most databases, while ILIKE (PostgreSQL) or COLLATE NOCASE (SQLite) makes it case insensitive.- Escaping: Use the ESCAPE clause or backslash (dialect-specific) to treat wildcard characters literally.- Index use: Leading wildcards (e.g., '%abc') often prevent index usage, causing full scans.- Unicode: Patterns operate on characters, not bytes, but combining characters can yield unexpected matches.Caveats:- Different dialects support different additional wildcards (e.g., MySQL supports '%' and '_' only; SQL Server also supports [] and [^]).- SIMILAR TO and regular expression functions offer richer pattern syntax and are sometimes confused with simple LIKE wildcards.

SQL WILDCARDS Syntax

-- Basic wildcard usage
SELECT column_list
FROM table_name
WHERE column LIKE pattern [ESCAPE escape_char];

SQL WILDCARDS Parameters

pattern (string) - The pattern containing wildcards to match against.
escape_char (single character, optional) - Defines an escape character to treat %, _ or other special characters literally.

Example Queries Using SQL WILDCARDS

-- 1. Find emails from any Gmail subdomain
SELECT email
FROM users
WHERE email LIKE '%@gmail.%';

-- 2. Telephone numbers whose area code starts with 4 and has any two digits after
SELECT phone
FROM contacts
WHERE phone LIKE '(4__) %';

-- 3. Escape a percent sign
SELECT comment
FROM reviews
WHERE comment LIKE '%95\%%' ESCAPE '\';

-- 4. PostgreSQL bracket wildcard example: any word starting with b and ending in t with exactly two middle letters
SELECT word
FROM dictionary
WHERE word LIKE 'b__t';

Expected Output Using SQL WILDCARDS

Each query returns only the rows where the target column matches the specified pattern, substituting wildcards for flexible text segments

Use Cases with SQL WILDCARDS

Searching for partial matches in free-text columns when exact values are unknown
Quickly locating records with shared prefixes or suffixes such as email domains, file extensions, or URL paths
Data quality checks to detect malformed values that do not fit an expected pattern
Generating ad-hoc reports that group similar values without writing complex regular expressions

Common Mistakes with SQL WILDCARDS

Forgetting that '%' matches zero characters, causing unintended matches like '' (empty strings)
Using leading '%' and then expecting indexes to be used, leading to slow scans
Confusing '_' with '%' and matching the wrong number of characters
Omitting proper escaping when literal '%' or '_' appears in the data
Assuming LIKE is case insensitive in every dialect

First Introduced In

SQL-92 (LIKE with % and _)

Frequently Asked Questions

What is the difference between % and _?

% matches any sequence of zero or more characters. _ matches exactly one character. Use them together for precise pattern control.

How can I make LIKE case insensitive?

In PostgreSQL use ILIKE. In MySQL or SQLite apply a case-insensitive collation or convert both sides with LOWER().

Do wildcards slow queries?

Leading wildcards usually prevent index usage, forcing full table scans. Trailing or middle wildcards may still allow index use depending on the database.

Can I use wildcards with numbers?

Yes. Numbers are implicitly cast to strings in the comparison. Ensure the column is stored as text or cast explicitly (e.g., phone::text) for predictable results.