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 winston@leetcode.com
2 Jonathan jonathanisgreat
3 Annabelle bella-@leetcode.com
4 Sally sally.come@leetcode.com
5 Marwan quarz#2020@leetcode.com
6 David teekre234@yahoo.com
Output:
user_id name mail
1 Winston winston@leetcode.com
3 Annabelle bella-@leetcode.com
4 Sally sally.come@leetcode.com
💡 Note:

Valid emails: winston@leetcode.com (starts with letter, valid characters), bella-@leetcode.com (starts with letter, dash allowed), sally.come@leetcode.com (starts with letter, period allowed). Invalid emails: jonathanisgreat (no @ domain), quarz#2020@leetcode.com (# not allowed), teekre234@yahoo.com (wrong domain).
Example 2 — Edge Cases with Numbers and Special Characters
Input Table:
user_id name mail
1 Alice a1b2c3@leetcode.com
2 Bob 1bob@leetcode.com
3 Charlie _charlie@leetcode.com
Output:
user_id name mail
1 Alice a1b2c3@leetcode.com
💡 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-Mails INPUT: Users Table user_id email 1 alice@leetcode.com 2 123bob@leet.com 3 tom_1@leetcode.com 4 mary@leet.code.com 5 john.doe@leetcode.com 6 @leetcode.com Valid email Invalid email Email Rules: - Start with letter [a-zA-Z] - Allow: letters, digits, _.- - End with @leetcode.com - No consecutive dots ALGORITHM: REGEXP 1 Use REGEXP_LIKE Pattern matching for email 2 Match Prefix ^[a-zA-Z] - starts with letter 3 Match Body [a-zA-Z0-9_.-]* chars 4 Match Domain @leetcode\.com$ at end SELECT * FROM Users WHERE REGEXP_LIKE( email, '^[a-zA-Z] [a-zA-Z0-9_.-]* @leetcode\\.com$' ) FINAL RESULT user_id email 1 alice@leetcode.com 3 tom_1@leetcode.com 5 john.doe@leetcode.com 3 Valid Users Matched pattern OK Rejected (3): 123bob - starts with digit mary - wrong domain @leet - no prefix before @ Pattern mismatch Key Insight: REGEXP_LIKE provides powerful pattern matching for email validation. The regex pattern ^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\.com$ ensures: (1) prefix starts with letter, (2) body contains only valid characters, (3) domain is exactly @leetcode.com. The backslash before the dot escapes it as a literal character, not a wildcard. Anchors ^ and $ ensure full string match. TutorialsPoint - Find Users With Valid E-Mails | Optimal Solution

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