MySQL - Wildcards

MySQL - Wildcards for Pattern Matching

The Role of Wildcards in SQL

In SQL, wildcards are special characters used with the LIKE operator to perform pattern matching in string comparisons. They are essential for building flexible search queries, such as finding all customers whose names start with ‘J’ or all products containing the word ‘-inch’. MySQL primarily provides two wildcards: the percent sign (%) and the underscore (_).

Wildcard	Description
%	Matches any sequence of zero or more characters. For example, ‘a%’ matches ‘a’, ‘apple’, and ‘application’.
_	Matches any single character. For instance, ‘h_t’ matches ‘hot’, ‘hat’, and ‘hit’, but not ‘heat’.

General Syntax

The basic syntax for using wildcards in a WHERE clause is as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'your_pattern';

Here are some common patterns combining both wildcards:

S.NO	Pattern & Description
1	WHERE name LIKE ‘Jo%‘ Finds any values that start with ‘Jo’.
2	WHERE name LIKE ‘%son%‘ Finds any values that contain ‘son’ anywhere in the string.
3	WHERE name LIKE ‘_o%‘ Finds any values that have ‘o’ as the second character.
4	WHERE name LIKE ‘J__n’ Finds any four-letter names that start with ‘J’ and end with ‘n’.
5	WHERE name LIKE ‘%a’ Finds any values that end with ‘a’.
6	WHERE name LIKE ‘C%i’ Finds any value that starts with ‘C’ and ends with ‘i’.

Setup: Creating a Sample Table

Let’s create a products table to demonstrate these concepts. Notice the use of modern data types and constraints.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    stock_quantity INT NOT NULL DEFAULT 0
);

-- Let's insert some sample data
INSERT INTO products (product_name, category, stock_quantity) VALUES
('Laptop 15-inch', 'Electronics', 50),
('Wireless Mouse', 'Electronics', 250),
('Mechanical Keyboard', 'Electronics', 120),
('Coffee Mug 12oz', 'Kitchenware', 300),
('T-shirt (Large)', 'Apparel', 150),
('T-shirt (Medium)', 'Apparel', 200),
('Notebook A5', 'Stationery', 500);

The Percent (%) Wildcard

The % wildcard is the most flexible, matching any number of characters (including none). It’s ideal for finding substrings.

Example 1: Finding products starting with ‘L’

SELECT product_name, category FROM products
WHERE product_name LIKE 'L%';

This query returns products whose names begin with ‘L’.

product_name	category
Laptop 15-inch	Electronics

Example 2: Finding products containing ‘board’

SELECT product_name, stock_quantity FROM products
WHERE product_name LIKE '%board%';

This query returns any product with ‘board’ anywhere in its name.

product_name	stock_quantity
Mechanical Keyboard	120

The Underscore (_) Wildcard

The _ wildcard is precise, matching exactly one character. It’s useful for patterns where you know the length of a portion of the string.

Example 3: Finding products with a specific pattern

Let’s find stationery items that are a type of ‘Notebook’, where the size is specified by two characters (like ‘A5’).

SELECT product_name, category FROM products
WHERE product_name LIKE 'Notebook __';

This matches ‘Notebook ’ followed by exactly two characters.

product_name	category
Notebook A5	Stationery

Escaping Wildcard Characters

What if you need to search for a literal ’%’ or ’_’ in your data? For example, finding products with ‘50%’ in their name. You can use the ESCAPE clause to specify an escape character.

Example 4: Searching for a literal percent sign

First, let’s add a product with a ’%’ in its name:

INSERT INTO products (product_name, category, stock_quantity)
VALUES ('Discount Coupon 50%', 'Offers', 1000);

Now, let’s find it. We’ll use the backslash \ as our escape character.

SELECT product_name FROM products
WHERE product_name LIKE '%50\%%' ESCAPE '\\';

This query will correctly find ‘Discount Coupon 50%‘.

product_name
Discount Coupon 50%

Best Practices and Performance

- **Avoid Leading Wildcards When Possible:** Queries like `WHERE column LIKE '%value'` are known as 'un-SARGable' because they cannot efficiently use a standard B-tree index on `column`. The database must perform a full table scan, which can be very slow on large tables.
- **Prefer Trailing Wildcards:** A query like `WHERE column LIKE 'value%'` can use an index on `column` and is much faster.
- **Case Sensitivity:** The behavior of `LIKE` (case-sensitive vs. case-insensitive) depends on the column's collation. Most default collations (like `utf8mb4_general_ci`) are case-insensitive (`'a%'` matches `'Apple'`). To force a case-sensitive search, you can use a binary collation: `WHERE column LIKE 'value%' COLLATE utf8mb4_bin;`.
- **Consider Full-Text Search:** For complex word and phrase searches in text, `LIKE` is inefficient. MySQL's built-in Full-Text Search (`MATCH() ... AGAINST()`) is a much better and more performant alternative.

Advanced Pattern Matching: REGEXP

For more complex patterns that LIKE cannot handle, MySQL provides the REGEXP (or RLIKE) operator, which uses regular expressions. This is an advanced topic but worth knowing.

Example 5: Using REGEXP to find products ending in a number

SELECT product_name FROM products
WHERE product_name REGEXP '[0-9]$';

This query finds product names that end with a digit (0-9).