Find Users With Valid Emails Problem


Description

LeetCode Problem 1517.

Table: Users

1
2
3
4
5
6
7
8
9
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
user_id is the primary key for this table.
This table contains information of the users signed up in a website. Some e-mails are invalid.

Write an SQL query to find the users who have valid emails.

A valid e-mail has a prefix name and a domain where:

  • The prefix name is a string that may contain letters (upper or lower case), digits, underscore ‘_’, period ‘.’ and/or dash ‘-‘. The prefix name must start with a letter.
  • The domain is ‘@leetcode.com’.

Return the result table in any order.

The query result format is in the following example.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Users
+---------+-----------+-------------------------+
| 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     | david69@gmail.com       |
| 7       | Shapiro   | .shapo@leetcode.com     |
+---------+-----------+-------------------------+

Result table:
+---------+-----------+-------------------------+
| user_id | name      | mail                    |
+---------+-----------+-------------------------+
| 1       | Winston   | winston@leetcode.com    |
| 3       | Annabelle | bella-@leetcode.com     |
| 4       | Sally     | sally.come@leetcode.com |
+---------+-----------+-------------------------+
The mail of user 2 doesn't have a domain.
The mail of user 5 has # sign which is not allowed.
The mail of user 6 doesn't have leetcode domain.
The mail of user 7 starts with a period.


MySQL Solution

1
2
3
select * 
from Users
where  mail regexp '^[A-Za-z][A-Za-z0-9\_\.\-]*@leetcode\.com$'




Related Posts

Sales By Day Of The Week Problem

LeetCode 1479. Write an SQL query to report how many...

Countries You Can Safely Invest In Problem

LeetCode 1501. Write an SQL query to find the countries...

Friendly Movies Streamed Last Month Problem

LeetCode 1495. Write an SQL query to report the distinct...

Find Users With Valid Emails Problem

LeetCode 1517. Write an SQL query to find the users...

Customer Order Frequency Problem

LeetCode 1511. Write an SQL query to report the customer_id...

Group Sold Products By The Date Problem

LeetCode 1484. Write an SQL query to find for each...