SQL Wildcards

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

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

Percent Sign (%): Represents zero or more characters.
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.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

SQL Wildcards

Introduction

What are Wildcards?

Common Wildcards

Syntax for Using Wildcards

Basic Syntax with LIKE

Step-by-Step Example

Sample Table

Inserting Sample Data

Using the Percent Sign (%)

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

Output

Using the Underscore (_)

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

Output

Combining Wildcards

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

Output

Using Wildcards with Other Operators

Example: Find employees whose email contains 'example'

Output

Using Wildcards in Different Scenarios

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

Output

Using NOT LIKE with Wildcards

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

Output

Conclusion

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Build REST APIs with Spring Boot 4, Spring Security 7, and JWT

[NEW] Learn Apache Maven with IntelliJ IDEA and Java 25

ChatGPT + Generative AI + Prompt Engineering for Beginners

Spring 7 and Spring Boot 4 for Beginners (Includes 8 Projects)

Building Real-Time REST APIs with Spring Boot - Blog App

Building Microservices with Spring Boot and Spring Cloud

Java Full-Stack Developer Course with Spring Boot and React JS

Build 5 Spring Boot Projects with Java: Line-by-Line Coding

Testing Spring Boot Application with JUnit and Mockito

Spring Boot Thymeleaf Real-Time Web Application - Blog App

Master Spring Data JPA with Hibernate

Spring Boot + Apache Kafka Course - The Practical Guide

Java Testing: Mastering JUnit 5 Framework

Reactive Programming in Java: Spring WebFlux and Testing

Spring Boot + RabbitMQ Course - The Practical Guide

Functional Programming in Java (Includes Java Collections)

ChatGPT for Java Developers: Boost Your Productivity with AI

Comments

Post a Comment

Spring Boot 3 Paid Course Published for Free on my Java Guides YouTube Channel

Spring Boot 3 Paid Course Published for Free
on my Java Guides YouTube Channel