SQL Wildcards

Introduction

In this chapter, we will focus on using wildcards in SQL. Wildcards are special characters used in SQL queries to search for data within a table. They are commonly used with the LIKE operator to perform pattern matching. This chapter will cover the definition, syntax, and provide examples to help you understand how to use wildcards effectively.

What are Wildcards?

Wildcards are special characters that can stand in for unknown characters in a text value. They are useful for performing searches and filtering data based on patterns rather than exact matches.

Common Wildcards

1. Percent Sign (%): Represents zero or more characters.
2. Underscore (_): Represents a single character.

Syntax for Using Wildcards

Basic Syntax with LIKE

SELECT columns
FROM table_name
WHERE column LIKE pattern;

• columns: The columns you want to retrieve.
• table_name: The table from which to retrieve the data.
• column: The column to search within.
• pattern: The pattern to match using wildcards.

Step-by-Step Example

Sample Table

First, let's create a sample table named employees.

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

Inserting Sample Data

INSERT INTO employees (first_name, last_name, email)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
('Sita', 'Patel', 'sita.patel@example.com'),
('Arjun', 'Singh', 'arjun.singh@example.com'),
('Priya', 'Sharma', 'priya.sharma@example.com'),
('Ravi', 'Verma', 'ravi.verma@example.com');

Using the Percent Sign (%)

The percent sign wildcard represents zero or more characters.

Example: Find employees whose first name starts with 'R'

SELECT * FROM employees
WHERE first_name LIKE 'R%';

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
5	Ravi	Verma	ravi.verma@example.com

Using the Underscore (_)

The underscore wildcard represents a single character.

Example: Find employees whose first name has 'a' as the second character

SELECT * FROM employees
WHERE first_name LIKE '_a%';

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com

Combining Wildcards

You can combine wildcards to create more complex patterns.

Example: Find employees whose last name ends with 'a' and has at least one character before 'a'

SELECT * FROM employees
WHERE last_name LIKE '%a';

Output

id	first_name	last_name	email
4	Priya	Sharma	priya.sharma@example.com
5	Ravi	Verma	ravi.verma@example.com

Using Wildcards with Other Operators

You can use wildcards with other SQL operators to create more complex queries.

Example: Find employees whose email contains 'example'

SELECT * FROM employees
WHERE email LIKE '%example%';

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com
4	Priya	Sharma	priya.sharma@example.com
5	Ravi	Verma	ravi.verma@example.com

Using Wildcards in Different Scenarios

Example: Find employees whose first name is exactly 5 characters long

SELECT * FROM employees
WHERE first_name LIKE '_____';

Output

id	first_name	last_name	email
3	Arjun	Singh	arjun.singh@example.com
5	Ravi	Verma	ravi.verma@example.com

Using NOT LIKE with Wildcards

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

Example: Find employees whose last name does not start with 'S'

SELECT * FROM employees
WHERE last_name NOT LIKE 'S%';

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
5	Ravi	Verma	ravi.verma@example.com

Conclusion

Wildcards are used for performing pattern matching in SQL queries. This chapter covered the basic syntax for using wildcards with the LIKE operator, provided examples to illustrate their use, and demonstrated how to combine wildcards for more complex patterns. Understanding how to use wildcards effectively will greatly enhance your ability to query and filter data in a relational database.