sql_wildcards

SQL Wildcards

A wildcard character is a special character used in SQL to substitute for any other character(s) in a string. Wildcards are primarily used with the LIKE operator in a WHERE clause to search for specific patterns in string data.

Standard SQL Wildcard Characters

SQL defines two standard wildcard characters that are used with the LIKE operator for pattern matching:

• % (Percent Sign): Represents zero, one, or multiple characters.
• _ (Underscore): Represents a single character.

These wildcards make the LIKE operator more flexible than using simple equality (=) or inequality (!=) operators for string comparisons.

Syntax with LIKE:

SELECT column_list
FROM table_name
WHERE column_name LIKE 'pattern';

Demo Database

For our examples, we will use a conceptual “Customers” table.

Sample “Customers” table data:

CustomerID | CustomerName                        | City        | Country
---------- | ----------------------------------- | ----------- | -------
1          | Alfreds Futterkiste               | Berlin      | Germany
2          | Ana Trujillo Emparedados y helados  | México D.F. | Mexico
3          | Antonio Moreno Taquería             | México D.F. | Mexico
4          | Around the Horn                     | London      | UK
5          | Berglunds snabbköp                | Luleå       | Sweden
6          | Blauer See Delikatessen             | Mannheim    | Germany

Using the SQL % Wildcard

The % wildcard matches any sequence of characters (including an empty sequence).

Example 1: Find customers whose City starts with “ber”:

SELECT CustomerName, City
FROM Customers
WHERE City LIKE 'ber%';

This would match ‘Berlin’, ‘Berkeley’, etc.

Example 2: Find customers whose CustomerName contains the pattern “er”:

SELECT CustomerName, City
FROM Customers
WHERE CustomerName LIKE '%er%';

This would match ‘Alfreds Futterkiste’, ‘Berglunds snabbköp’, ‘Blauer See Delikatessen’, etc.

Example 3: Find customers whose Country ends with “y”:

SELECT CustomerName, Country
FROM Customers
WHERE Country LIKE '%y';

This would match ‘Germany’.

Using the SQL _ Wildcard

The _ wildcard matches any single character.

Example 1: Find customers whose City starts with any character, followed by “ondon”:

SELECT CustomerName, City
FROM Customers
WHERE City LIKE '_ondon';

This would match ‘London’.

Example 2: Find customers whose City starts with “L”, followed by any two characters, then “d”, then any character, then “n”:

SELECT CustomerName, City
FROM Customers
WHERE City LIKE 'L__d_n%';

This could match ‘London’ if the full pattern were ‘L_nd_n’. With ‘L__d_n%’, it would match cities like ‘Luddenham’ (if it existed and matched). For ‘London’, the pattern L_n_on (from original) is more specific.

Let’s use a clearer example: Find cities that are 6 characters long and end with ‘in’:

SELECT DISTINCT City
FROM Customers
WHERE City LIKE '____in';

This would match ‘Berlin’.

Using NOT LIKE

You can use NOT LIKE to find strings that do not match a pattern.

Example: Find customers whose City does not start with “B”:

SELECT CustomerName, City
FROM Customers
WHERE City NOT LIKE 'B%';

Escaping Wildcard Characters

If you need to search for a literal percent sign (%) or underscore (_) in your data, you need to use an escape character. The ESCAPE keyword specifies the escape character.

Example: Find products with ‘50%’ in their name (assuming “ is the escape character):

SELECT ProductName
FROM Products
WHERE ProductName LIKE '%50\%%' ESCAPE '\';

Common escape characters are backslash (\) or sometimes others like ! or #.

Beyond Basic Wildcards: Regular Expressions

While % and _ are standard and widely supported, some database systems (like MySQL, PostgreSQL, Oracle, and SQL Server with specific settings) offer more powerful pattern matching through regular expressions. Operators like REGEXP, RLIKE, SIMILAR TO, or functions like REGEXP_LIKE() allow for much more complex pattern definitions.

For example, finding cities that start with ‘B’, ‘S’, or ‘P’ (which was previously done with non-standard [bsp]%) could be achieved with regular expressions if your RDBMS supports it (e.g., WHERE City REGEXP '^[bsp]' in MySQL or WHERE City ~ '^[bsp]' in PostgreSQL).

Character list wildcards like [charlist] or [^charlist] are not part of the ANSI SQL standard and their support varies by database system. For general portability, stick to % and _, or use regular expressions where supported and necessary.