SQL Wildcards
SQL's wildcards live inside LIKE patterns. SQL Server and MS Access add a couple more on top of the standard pair.
Standard wildcards (all databases)
| Wildcard | Means | Example |
|---|---|---|
% | Zero or more characters. | LIKE 'A%' |
_ | Exactly one character. | LIKE 'A_a' |
SQL Server / MS Access extras
| Wildcard | Means | Example |
|---|---|---|
[abc] | One character in the set. | LIKE '[ABC]%' |
[a-z] | One character in a range. | LIKE '[0-9]%' |
[^abc] | One character not in the set. | LIKE '[^AEIOU]%' |
MS Access flavour
MS Access uses * and ? instead of % and _ when running queries through the Access UI — but via ADO/OLEDB it accepts the standard ones too.
When wildcards aren't enough
For real pattern matching — anchors, alternation, character classes — use the engine's regular expression support: REGEXP in MySQL/SQLite, ~ in PostgreSQL, LIKE with % + a CASE in SQL Server.
Tip: Treat
LIKE '%foo%' as "scan the whole table". For frequent contains-search on large tables, add a full-text index (MySQL FULLTEXT, Postgres tsvector).Example
Exercise
In LIKE, match exactly one character with…
LIKE 'A
a'
A single underscore.
Discussion
Loading…