Find Users With Valid E-Mails - Problem

You are given a Users table containing user information including their email addresses. Some of these emails are invalid and need to be filtered out.

Write a SQL query to find users who have valid emails.

A valid email must satisfy these conditions:

  • The prefix name (before @) must start with a letter
  • The prefix can contain letters (upper/lower case), digits, underscore '_', period '.', and/or dash '-'
  • The domain must be exactly @leetcode.com

Return the result in any order.

Table Schema

Users
Column Name Type Description
user_id PK int Primary key, unique identifier for each user
name varchar User's name
mail varchar User's email address (may be invalid)
Primary Key: user_id
Note: Contains user signup information with some invalid email addresses

Input & Output

Example 1 — Mixed Valid and Invalid Emails
Input Table:
user_id name mail
1 Winston [email protected]
2 Jonathan jonathanisgreat
3 Annabelle [email protected]
4 Sally [email protected]
5 Marwan quarz#[email protected]
6 David [email protected]
Output:
user_id name mail
1 Winston [email protected]
3 Annabelle [email protected]
4 Sally [email protected]
💡 Note:

Valid emails: [email protected] (starts with letter, valid characters), [email protected] (starts with letter, dash allowed), [email protected] (starts with letter, period allowed). Invalid emails: jonathanisgreat (no @ domain), quarz#[email protected] (# not allowed), [email protected] (wrong domain).
Example 2 — Edge Cases with Numbers and Special Characters
Input Table:
user_id name mail
1 Alice [email protected]
2 Bob [email protected]
3 Charlie [email protected]
Output:
user_id name mail
1 Alice [email protected]
💡 Note:

Only Alice's email is valid because it starts with a letter 'a'. Bob's email starts with digit '1' (invalid), and Charlie's email starts with underscore '_' (invalid). The prefix must start with a letter.

Constraints

  • 1 ≤ user_id ≤ 1000
  • name and mail are valid strings
  • mail contains valid ASCII characters

Visualization

Tap to expand
Find Users With Valid E-MailsInput: All Usersuser_idnamemail1Winston[email protected]2Jonathanjonathanisgreat3Annabelle[email protected]4Sally[email protected]REGEXPFILTEROutput: Valid Emails Onlyuser_idnamemail1Winston[email protected]3Annabelle[email protected]4Sally[email protected]Pattern: ^[a-zA-Z][a-zA-Z0-9._-]*@leetcode\.com$✗ Missing domain✓ Valid format
Understanding the Visualization
1
Input Table
Users with mixed email formats
2
Regex Pattern
Filter by email validation rules
3
Valid Results
Only properly formatted emails
Key Takeaway
🎯 Key Insight: Regular expressions efficiently validate complex string patterns in a single WHERE clause condition

Related Problems

Asked in
Facebook 38 Amazon 25 Microsoft 22
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen