Find Valid Emails - Problem
You are given a table called Users that contains user information including their email addresses.
Write a SQL query to find all valid email addresses that meet the following criteria:
- Contains exactly one @ symbol
- Ends with .com
- The part before @ contains only alphanumeric characters and underscores
- The part after @ and before .com contains only letters (domain name)
Return the result table ordered by user_id in ascending order.
Table Schema
Users
| Column Name | Type | Description |
|---|---|---|
user_id
PK
|
int | Unique identifier for each user |
email
|
varchar | Email address of the user |
Primary Key: user_id
Note: Each row contains a user's unique ID and email address
Input & Output
Example 1 — Mixed Valid and Invalid Emails
Input Table:
| user_id | |
|---|---|
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
| 4 | invalid@test |
| 5 | [email protected] |
Output:
| user_id | |
|---|---|
| 1 | [email protected] |
| 3 | [email protected] |
💡 Note:
Valid emails: [email protected] (alphanumeric before @, letters in domain), [email protected] (follows all rules). Invalid emails: [email protected] (no domain name), invalid@test (doesn't end with .com), [email protected] (numbers in domain name).
Example 2 — Special Characters and Underscores
Input Table:
| user_id | |
|---|---|
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
| 4 | double@@email.com |
Output:
| user_id | |
|---|---|
| 1 | [email protected] |
| 3 | [email protected] |
💡 Note:
Valid: [email protected] (underscore allowed before @), [email protected] (meets all criteria). Invalid: [email protected] (hyphen not allowed before @), double@@email.com (has two @ symbols).
Constraints
-
1 ≤ user_id ≤ 1000 -
emailcontains only printable ASCII characters - Each email is at most 100 characters long
Visualization
Tap to expand
Understanding the Visualization
1
Input
Users table with mixed email formats
2
Pattern Match
Apply REGEXP validation rules
3
Output
Only emails meeting all criteria
Key Takeaway
🎯 Key Insight: Use REGEXP for complex string validation patterns in a single condition
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code