sql-wildcard-operators

SQL - Wildcard Operators with LIKE

Wildcard operators are used with the LIKE operator to perform pattern matching on string data. They allow you to create flexible search conditions when you don’t know the exact value you’re looking for.

SQL supports two primary wildcard operators:

Wildcard	Description
% (Percent Sign)	Matches any sequence of zero or more characters.
_ (Underscore)	Matches any single character.

Note: Some older systems like MS Access use * instead of % and ? instead of _, but % and _ are the standard.

Common Patterns

Here are some practical examples of how these wildcards can be combined:

LIKE Pattern	Description
'Sm%'	Finds values starting with ‘Sm’ (e.g., ‘Smith’, ‘Small’).
'%th'	Finds values ending with ‘th’ (e.g., ‘Smith’, ‘Month’).
'%art%'	Finds values containing ‘art’ anywhere (e.g., ‘Carter’, ‘Artichoke’).
'J_ne'	Finds values that start with ‘J’, end with ‘ne’, and have exactly one character in between (e.g., ‘Jane’, ‘June’).
'__a%'	Finds values with ‘a’ as the third character (e.g., ‘Frank’, ‘Maria’).

Example

Let’s use a Customers table to demonstrate:

+----+-----------+-----------+
| ID | Name      | City      |
+----+-----------+-----------+
|  1 | John Smith| New York  |
|  2 | Jane Doe  | London    |
|  3 | Sam Brown | Tokyo     |
|  4 | Jon Stewart | New York|
|  5 | Art Vandelay| New York|
+----+-----------+-----------+

The following query finds all customers whose name starts with ‘J’ and has ‘n’ as the third letter.

SELECT * FROM Customers
WHERE Name LIKE 'J_n%';

This would produce the following result:

+----+-------------+----------+
| ID | Name        | City     |
+----+-------------+----------+
|  1 | John Smith  | New York |
|  2 | Jane Doe    | London   |
|  4 | Jon Stewart | New York |
+----+-------------+----------+

Notice how it matches both ‘John’ and ‘Jane’ because _ matches a single character (‘o’ or ‘a’), and ‘Jon’ because the final % matches zero or more characters.

Performance and Case-Sensitivity

Remember that LIKE queries, especially those with a leading wildcard ('%text'), can be inefficient as they may prevent the database from using an index. For advanced text searching, consider using the full-text search capabilities of your RDBMS. Also, the case-sensitivity of LIKE depends on your database’s collation settings.