Question
Table name: ``
| Column Name | Type |
|---|---|
| user_id | int |
| name | varchar |
| varchar |
This table contains information of the users signed up in a website. Some e-mails are invalid.
A valid e-mail has a prefix name and a domain.
domain: @leetcode.com.
prefix name is a string that may contain:
- letters (upper or lower case)
- digits
- underscore
_ - period
. - dash
-
(The prefix name must start with a letter.)
Write a solution to find the users who have valid emails.
Return the result table in any order.
Explaination
For string validation, there is a powerful tool for pattern matching called RegEx.
In SQL, it provides the keyword REGEXP to enter your pattern for validation.
Here, we simply explain the patterns we used to match the requirement.
| Pattern | Meaning |
|---|---|
^ |
Match Beginning of string |
$ |
Match The End of string |
[a-zA-Z] |
Match any upper and lower case letter |
[a-zA-Z0-9_.-] |
Match any upper and lower case letter, digits, underscore, period, dash |
* |
Zero or more instances of string preceding it |
Therefore, ^[a-zA-Z][a-zA-Z0-9_.-]* means:
Begins the string with any upper or lower case letter,
follows by zero or more upper and lower case letter, digits, underscore, period, and/or dash.
Solution
SELECT * FROM Users
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@leetcode\\.com$'
More SQL 50 questions: here
Please cite the source for reprints, feel free to verify the sources cited in the article, and point out any errors or lack of clarity of expression. You can comment in the comments section below or email to GreenMeeple@yahoo.com