Find Users With Valid E-Mails | Leetcode 1517

This chapter covers the final challenge of the 50-question SQL interview series on LeetCode (Problem 1517). We solve the problem of filtering user records based on strict syntactic requirements for email addresses using Regular Expressions (REGEXP) in MySQL.

1. Why Do We Need Regular Expressions? (Problem Statement & Status Quo)

In standard SQL queries, filtering text columns typically relies on basic operators like LIKE, IN, or simple equality checks (=). For example:

• WHERE mail LIKE '%@gmail.com'

However, when business logic demands strict validation rules—such as requiring a prefix to start specifically with an alphabetic character, permitting only certain special characters (periods, dashes, underscores), and ensuring exact structural formatting—standard LIKE patterns become exceedingly cumbersome, fragile, and difficult to maintain.

To solve this efficiently, SQL provides Regular Expressions (REGEXP), which allow us to define intricate search patterns and validation rules compactly in a single string.

2. Table Schema and Problem Constraints

We are given a single table named users containing information about users who signed up on a website. Some email addresses are invalid and must be filtered out.

Table: users

Valid Email Requirements:

1. Prefix Rule: Must start with a letter (upper or lower case: a-z, A-Z).
2. Allowed Characters: The prefix may contain letters, digits (0-9), underscores (_), periods (.), and dashes (-).
3. Domain Rule: Must end with the exact domain @leetcode.com.

3. Core Mental Model: Visualizing Valid vs. Invalid Emails

Let's evaluate a sample dataset to understand why certain emails pass validation while others fail.

Filtering Walkthrough:

• winston@leetcode.com: Kept. Matches all criteria.
• marwan#@leetcode.com: Dropped. Contains a # symbol, which violates the allowed character set.
• david_t@gmail.com: Dropped. Domain is @gmail.com instead of @leetcode.com.
• .shapiro@leetcode.com: Dropped. Starts with a period (.) rather than an upper/lower case letter.

4. Naive Approach vs. Regular Expression Approach

Attempting to validate complex string patterns using standard SQL string manipulation leads to nested, unreadable queries.

The Naive (Unscalable) Approach:

-- Trying to handle constraints with multiple LIKE conditions is error-prone and brittle
SELECT * 
FROM users 
WHERE mail LIKE '%@leetcode.com'
  AND mail NOT LIKE '%#%'
  AND mail NOT LIKE '%-%' -- (if dashes had restrictions)
  -- Fails to easily enforce starting letters or strict character classes

The Production-Grade REGEXP Approach:

Using MySQL's regular expression matching (REGEXP), we can encapsulate the entire state machine of valid characters into a single concise pattern.

5. Anatomy of the MySQL Regular Expression Pattern

Constructing a regex pattern requires specific anchor symbols and character classes. Let's break down each component used in our final query:

^ [A-Za-z] [A-Za-z0-9_.-]* @ leetcode \. com $

6. Step-by-Step Evolution of the SQL Query

Step 1: Selecting the Table and Columns

The prompt requires returning all columns for users with valid emails.

SELECT *
FROM users

Step 2: Applying the REGEXP Condition

We apply the REGEXP operator to the mail column, wrapping our regex pattern inside single quotes.

SELECT *
FROM users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com$'

Note on Escaping in MySQL: In standard regex, . matches any character. To match a literal dot before com, we escape it with a backslash. In MySQL string literals, backslashes must often be doubled (\\.) depending on the SQL client parser, or written as [.] inside character classes.

7. Complete Solution Code

Here is the final, accepted SQL query for LeetCode 1517:

-- Find Users With Valid E-Mails
SELECT user_id, name, mail
FROM users
WHERE mail REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\\.com$';

8. Summary and Key Takeaways

• Problem Completion: Successfully finishes the 50-question SQL interview series on LeetCode.
• Regular Expressions (REGEXP): The ultimate tool for complex pattern matching and string validation in SQL.
• Start Anchor (^): Ensures validation begins strictly at the first character of the string.
• End Anchor ($): Ensures validation terminates cleanly at the final character, preventing trailing garbage data.
• Character Classes ([...]): Defines allowed sets of characters (e.g., [A-Za-z] for alphabetic letters, [0-9] for digits).
• Escaping Special Characters (\\.): Essential when matching literal characters in regex that otherwise have special operational meanings (like the period . wildcard).
• Interview Readiness: Mastering string functions, window functions, joins, aggregations, and regex across these 50 questions provides a rock-solid foundation to clear any SQL technical interview.