NUHMAN.com

📘 Description:

Explore the SQL query that uses regular expressions (REGEXP) to filter email addresses in the users table, ensuring they match a specific pattern. This article provides a step-by-step breakdown of the query's components and how it validates emails.

🔍 Introduction

In SQL, the REGEXP operator allows us to perform pattern matching using regular expressions. This powerful tool is especially useful when working with string data, such as email addresses. In this article, we’ll break down an SQL query that selects all users from the users table where the email addresses match a particular format.

SQL Query:

sql

SELECT * 
FROM   users AS u 
WHERE  u.mail REGEXP '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode.com$';

Let’s explore each part of this query and understand how it works.

1️⃣ SELECT *

The SELECT * part of the query means that all columns from the resulting rows will be returned. This allows you to retrieve all the data for users whose email addresses match the specified pattern.

2️⃣ FROM users AS u

Here, FROM users AS u specifies that the data will be fetched from the users table, and the alias u is assigned to the table for shorthand reference throughout the query. The alias simplifies the query, especially when working with more complex joins or subqueries.

3️⃣ WHERE u.mail REGEXP '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode.com$'

This WHERE clause filters the rows based on the email pattern. Specifically, it uses the REGEXP operator to match the values in the mail column against the regular expression ^[a-zA-Z][a-zA-Z0-9._-]*@leetcode.com$.

📍 Breaking Down the Regular Expression:

The regular expression '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode.com$' is used to validate email addresses with the domain @leetcode.com. Let’s break it down:

1. ^:
2. The caret (^) asserts that the match must start at the beginning of the string. It ensures that the email address starts with the specified pattern.
3. [a-zA-Z]:
4. This part matches a single letter, either uppercase (A-Z) or lowercase (a-z). The email address must start with a letter, not a number or symbol.
5. [a-zA-Z0-9._-]*:
6. This part matches zero or more occurrences of:

• Letters (A-Z, a-z)
• Digits (0-9)
• Periods (.)
• Underscores (_)
• Hyphens (-)

1. This segment represents the local part of the email address, allowing for a variety of valid characters in the email address (after the first letter).
2. @leetcode.com:
3. This matches the literal string @leetcode.com, ensuring that the domain of the email address must be exactly leetcode.com.
4. $:
5. The dollar sign ($) asserts that the match must occur at the end of the string. It ensures the email address ends with @leetcode.com, without any additional characters or domains.

📍 Examples of Matching Email Addresses:

Here are some examples of email addresses that would match the pattern defined by the regular expression:

• alice@leetcode.com
• bob.smith@leetcode.com
• charlie_brown-123@leetcode.com

These emails all start with a letter, have a valid combination of allowed characters, and end with the domain @leetcode.com.

📍 Examples of Non-Matching Email Addresses:

Here are some examples of email addresses that do not match the pattern:

• 1alice@leetcode.com: This email starts with a number, which is not allowed by the regex.
• alice@otherdomain.com: The domain is incorrect (should be @leetcode.com).
• alice@leetcode: The domain is incomplete and doesn’t include .com.
• alice@leetcode.com.org: The domain contains extra parts after @leetcode.com.

🧑‍💻 Use Case for This Query:

This query is particularly useful when you want to filter users based on a specific email format or domain. For example, if you're looking for users who registered with an email address from leetcode.com, this query will ensure you only retrieve emails from that domain, with proper formatting.

✅ Conclusion

The SQL query SELECT * FROM users WHERE u.mail REGEXP '^[a-zA-Z][a-zA-Z0-9._-]*@leetcode.com$'; effectively filters the users table to only include users with email addresses that match the specified pattern. Using the REGEXP operator allows for powerful string matching and validation directly within your SQL queries.