Using Wildcard Characters in SQL: A Comprehensive Guide
☰Fullscreen
Table of Content:
Wildcard Characters in MS Access
| Symbol | Description | Example |
|---|---|---|
| * | Represents zero or more characters | mu* finds mu, music, multiply, and mutton |
| ? | Represents a single character | h?t finds hot, hat, and hit |
| [] | Represents any single character within the brackets | h[oa]t finds hot and hat, but not hit |
| ! | Represents any character not in the brackets | h[!oa]t finds hit, but not hot and hat |
| - | Represents a range of characters | c[a-b]t finds cat and cbt |
| # | Represents any single numeric character | 1#5 finds 105, 115, 125, 135, 145, 155, 165, 175, 185, and 195 |
Wildcard Characters in SQL Server
| Symbol | Description | Example |
|---|---|---|
| % | Represents zero or more characters | mu% finds mu, music, multiply, and mutton |
| _ | Represents a single character | h_t finds hot, hat, and hit |
| [] | Represents any single character within the brackets | h[oa]t finds hot and hat, but not hit |
| ^ | Represents any character not in the brackets | h[^oa]t finds hit, but not hot and hat |
| - | Represents a range of characters | c[a-b]t finds cat and cbt |
Here are some examples showing different LIKE operators with '%' and '_' wildcards:
| LIKE Operator | Description |
|---|---|
| WHERE CustomerName LIKE 'b%' | Finds any values that starts with "b" |
| WHERE CustomerName LIKE '%b' | Finds any values that ends with "b" |
| WHERE CustomerName LIKE '%or%' | Finds any values that have "or" in any position |
| WHERE CustomerName LIKE '_p%' | Finds any values that have "p" in the second position |
| WHERE CustomerName LIKE 'b_%_%' | Finds any values that starts with "b" and are at least 3 characters in length |
| WHERE ContactName LIKE 'b%o' | Finds any values that starts with "b" and ends with "o" |
All the wildcards can also be used in combinations!