Free tools Windows power users keep installed
One-click scans. No signup required.
Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
To find rows where a string column begins with a value, use LIKE followed by %:
SELECT id, username
FROM users
WHERE username LIKE 'adm%';This matches values such as admin and admiral, but not superadmin. The result’s case and accent behavior depends on the column and expression collation.
How the prefix pattern works
In a LIKE pattern, % matches zero or more characters. Put it after the prefix to search from the start of a value:
-- Begins with adm
WHERE username LIKE 'adm%'
-- Contains adm anywhere
WHERE username LIKE '%adm%'
-- Ends with adm
WHERE username LIKE '%adm'The underscore wildcard, _, matches exactly one character. For example, 'adm_n%' can match admin and adman. If you need either wildcard as a literal character, escape it as described below.
#1 Best Overall
= does not interpret % as a wildcard. username = 'adm%' looks for the literal value adm%; use LIKE for a pattern.
For example, with names Alice, Alison, Bob, and Malice, name LIKE 'Ali%' matches the first two under a collation that treats their letters as equivalent. Malice does not match because Ali is not at the start.
Use a variable prefix safely
When the prefix comes from application input, bind it as a value rather than inserting it into SQL text:
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsSELECT id, username
FROM users
WHERE username LIKE CONCAT(?, '%');Here, ? is a parameter marker; bind the prefix through your database driver’s prepared-statement API. This protects the SQL statement from input being interpreted as SQL. It does not, by itself, make % and _ in the supplied value literal: those remain LIKE wildcards. If users expect a literal prefix, escape wildcard characters and the chosen escape character using a strategy that matches your connection’s SQL mode and character set, then append the pattern wildcard in a controlled way.
Parameters bind values, not identifiers. A placeholder cannot safely stand in for a table or column name; use a controlled allowlist if the application must select an identifier dynamically.
You can also compare one column against a prefix stored in another table:
SELECT a.id
FROM table_a AS a
JOIN table_b AS b
ON a.value LIKE CONCAT(b.prefix, '%');This is a different optimization shape from searching for a fixed or bound prefix. Check the actual plan with EXPLAIN.
Case sensitivity depends on collation
For nonbinary strings, LIKE follows the character set and collation rules applicable to the column and expression. Many commonly used collations are case-insensitive, but not all MySQL installations, columns, or expressions behave the same way. A case-insensitive collation may also be accent-insensitive, so distinct spellings can compare as equivalent. See MySQL’s case-sensitivity and collation guidance.
To request case-sensitive matching for a query, apply a compatible case-sensitive collation, for example:
SELECT id, username
FROM users
WHERE username COLLATE utf8mb4_0900_as_cs LIKE 'adm%';The column’s character set must be compatible with the selected collation. For a permanent rule, consider defining the column with the intended collation rather than overriding it in every query. Binary strings compare by byte values, which is case-sensitive for alphabetic bytes but may not express the linguistic rules wanted for human text.
Rank #3
Inspect the column rather than assuming connection defaults describe it:
SHOW FULL COLUMNS FROM users;
SELECT @@character_set_connection, @@collation_connection;Connection settings are useful context, but the column and expression collations determine comparison behavior. When joining or comparing string columns, compatible character sets and collations can also avoid unwanted conversions; MySQL discusses this in its character-set optimization guidance.
Literal percent signs and underscores
If the intended prefix is 100%, an unescaped percent sign would match any continuation rather than a literal percent sign. In the usual backslash-escape form, the pattern can be written:
SELECT product_code
FROM products
WHERE product_code LIKE '100\%%';The first escaped percent is literal; the final percent is the wildcard for the rest of the value. Escape _ too when it is meant literally. Backslash handling can depend on SQL mode and connection settings, so verify the chosen escape convention for your application and database configuration. For dynamic input, escape the user-supplied prefix before binding it; parameterization and pattern escaping solve different problems.
When to use a regular expression
For a simple prefix, LIKE 'adm%' is direct and easy to read. Use a regular expression when the requirement actually needs regular-expression features. In current MySQL documentation, the function is REGEXP_LIKE():
Recommended Free Tools
SELECT id, username
FROM users
WHERE REGEXP_LIKE(username, '^adm');The ^ anchor means the match starts at the beginning. Without it, REGEXP_LIKE(username, 'adm') can match text in the middle of a value. Regex case behavior also depends on options and collation; for a case-sensitive regex, MySQL documents an option such as 'c':
WHERE REGEXP_LIKE(username, '^adm', 'c');Function availability and terminology can vary across MySQL versions; consult the documentation for the version you run. Do not assume a regular expression is faster or slower than LIKE for every workload—compare plans and timings on representative data.
Indexes and query plans
For frequent prefix lookups on a bounded string column, a normal index is a sensible starting point:
CREATE INDEX idx_users_username ON users (username);A pattern such as LIKE 'adm%' has a fixed beginning that can make an ordinary B-tree index useful. A leading wildcard, as in LIKE '%adm%', does not provide the same starting boundary. Neither pattern guarantees a particular index plan: table size, selectivity, collation, query shape, and optimizer estimates all matter.
Check the plan for your query and data:
EXPLAIN
SELECT id, username
FROM users
WHERE username LIKE 'adm%';Review fields such as possible_keys, key, estimated rows, and access type. An index may not be selected when it would be less efficient than another plan. MySQL’s column-index documentation covers index options and constraints.
A TEXT column generally needs a prefix index, for example:
CREATE INDEX idx_documents_title
ON documents (title(100));That indexes only the beginning of each value, so it can save space but may distinguish rows poorly when many titles share the same first characters. For nonbinary strings, the prefix length in the definition is expressed in characters; underlying index limits are measured in bytes. Multibyte character sets therefore matter when choosing a length. See MySQL’s CREATE INDEX reference. For an ordinary bounded VARCHAR, a full-column index is often simpler, but the right choice depends on schema and workload.
Searches that apply a function to the indexed column, such as LEFT(username, 3) = 'adm' or SUBSTRING(username, 1, 3) = 'adm', are not automatically equivalent in index use to a LIKE prefix. Use them when their logic is useful, and inspect EXPLAIN rather than assuming performance.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Common edge cases
- NULL values:
username LIKE 'adm%'does not select a row whereusernameisNULL. If missing values should be included, addOR username IS NULL;NULLis not an empty string. - Empty prefix:
LIKE '%'matches every non-NULLstring. Handle an empty search field separately if returning the whole table is not intended. - Fixed-length
CHAR: Test trailing-space behavior with your column type and collation rather than assuming it is identical toVARCHAR. - Contains search:
LIKE '%term%'is a contains condition, not a prefix search. Full-text search is intended for word-oriented document searches, not usually arbitrary character-prefix lookups. Search-as-you-type at high volume or fuzzy matching may call for a dedicated search design, with the extra synchronization and operational work that entails. - Range tricks: Replacing
LIKEwith calculated range bounds is not a generic shortcut. Character sets, collation ordering, and successor calculation make such ranges model-specific.
For a routine “starts with” condition, begin with LIKE 'prefix%', set the intended collation semantics, bind dynamic values, and use EXPLAIN to validate performance.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
