SQL - Wildcards

SQL Wildcards for Pattern Matching

Introduction to SQL Wildcards

In SQL, wildcards are special characters used with the LIKE operator to search for patterns in string data. This allows for flexible and powerful filtering in the WHERE clause, going beyond simple equality checks.

The two standard wildcards defined by ANSI SQL are:

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

These wildcards can be combined to create complex search patterns for sophisticated data retrieval.

Syntax

The basic syntax for using wildcards is as follows:

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

Here are some common examples demonstrating how to use wildcards with the LIKE operator:

Statement	Description
WHERE name LIKE ‘A%‘	Finds any values that start with ‘A’.
WHERE name LIKE ‘%a’	Finds any values that end with ‘a’.
WHERE name LIKE ‘%om%‘	Finds any values that have ‘om’ in any position.
WHERE name LIKE ‘_o%‘	Finds any values that have ‘o’ in the second position.
WHERE name LIKE ‘K__l%‘	Finds any values that start with ‘K’, are at least 4 characters long, and have ‘l’ as the fourth character.
WHERE name LIKE ‘R%h’	Finds any values that start with ‘R’ and end with ‘h’.

Practical Example

Let’s set up a sample CUSTOMERS table to demonstrate these concepts.

-- Modern DDL (Data Definition Language) for creating the table
CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    AGE INT NOT NULL,
    CITY VARCHAR(255),
    SALARY DECIMAL(18, 2)
);

-- Inserting sample data
INSERT INTO CUSTOMERS (ID, NAME, AGE, CITY, SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

Here’s a query to find all customers whose name starts with ‘K’:

SELECT ID, NAME, CITY FROM CUSTOMERS
WHERE NAME LIKE 'K%';

Output

ID	NAME	CITY
2	Khilan	Delhi
3	Kaushik	Kota
6	Komal	Hyderabad

Best Practices and Advanced Concepts

Escaping Wildcard Characters

What if you need to search for the ’%’ or ’_’ characters themselves? You can use the ESCAPE clause to specify an escape character.

-- Find products with '50%' in their name
SELECT product_name FROM products
WHERE product_name LIKE '%50\%%' ESCAPE '\';

Case Sensitivity

The behavior of LIKE regarding case sensitivity is not standard and depends on the database system and its collation settings:

• PostgreSQL: LIKE is case-sensitive. Use ILIKE for case-insensitive matching.
• MySQL: Behavior depends on the column’s collation. Most default collations (like utf8mb4_general_ci) are case-insensitive.
• SQL Server: Behavior also depends on the database collation. Default is typically case-insensitive.

Performance Considerations

Avoid using a leading wildcard (%search_term) whenever possible. Queries with a leading wildcard cannot use a standard B-tree index on that column, which can lead to a full table scan and poor performance on large tables.

Security Note: SQL Injection

Never concatenate user input directly into a LIKE clause. This creates a risk of SQL Injection. Always use parameterized queries (prepared statements) to safely handle user input.

Beyond LIKE: Regular Expressions

For more complex pattern matching, most modern databases support regular expressions via operators like REGEXP (MySQL), ~ (PostgreSQL), or functions like REGEXP_LIKE (Oracle, SQL Server). These offer significantly more power than the standard wildcards.