SQL solutions - regular expressions
Fun with regular expressions!  The fact that this question deals with character "types," i.e., alphanumeric, letter, etc., is an indicator that regular expressi
Fun with regular expressions!  The fact that this question deals with character "types," i.e., alphanumeric, letter, etc., is an indicator that regular expressions are the key here.  There was a question about regular expressions a handful of weeks ago ([3415. Find Products with Three Consecutive Digits](https://leetcode.com/problems/find-products-with-three-consecutive-digits/description/)), so tackling that, along with familiarizing yourself with regular expressions, would be a good place to start.  This problem here had the "Easy" label but I thought it did have some tricky components.  Let's dive into how to tackle this one...

# Approach
Some quick notes here that'll add up to the solution:
\- SQL doesn't have a great "count instances of character in string" so use `length(<raw field>) - length(replace(<raw field>, '<character>', ''))` to do that.  Here the character is the @ symbol, and there should be one and only one in the email.
\- Use `instr()` (Oracle, MySQL), `charindex()` (MSSQL), or `position()` to get the character position of that single @ (examine the solution code for more specifics on the differences between each variant).  Let's call that position "at_pos" (I do not use that alias in the code but it makes the below easier to explain):
\-\- Take `substr`/`substring(email, 1, at_pos - 1)` to get the characters before the @, and I also apply uppercase to make the character matching easier.  Perform regular expression **not like** `[^A-Z0-9]`, meaning that the string cannot contain a character that is both non-alpha (A-Z) and non-digit (0-9).
\-\- Take `substr`/`substring(email, at_pos + 1, 1)` to get the character directly after the @, and I also apply uppercase once again.  Perform regular expression **like** `[A-Z]`, as the first character after the @ must be a letter.
\- Since the domain after the @ must end in ".com", perform a simple `like '%.COM'`

Put those 4 conditions together and that'll yield valid emails.

Note that each SQL variant handles "invalid" substring arguments differently, such as negative indices or indices of 0; this solution performs the substring based on the position of the @ and I found MSSQL and Postgres to require valid substring arguments (that result in an actual substring) as opposed to Oracle and MySQL which accepted arguments that didn't result in a valid substring.  That's why you'll see a CTE used for the former 2, and the CTE filters emails to only those that have an @.

# Code
```Oracle []
/* Write your PL/SQL query statement below */

select user_id, email
from Users
where (LENGTH(email) - LENGTH(REPLACE(email, '@', ''))) = 1
and not REGEXP_LIKE(upper(substr(email, 1, instr(email, '@') - 1)), '[^A-Z0-9]')
and REGEXP_LIKE(upper(substr(email, instr(email, '@') + 1, 1)), '[A-Z]')
and upper(email) like '%.COM';
```
```MySQL []
# Write your MySQL query statement below

select user_id, email
from Users
where (LENGTH(email) - LENGTH(REPLACE(email, '@', ''))) = 1
and not REGEXP_LIKE(upper(substr(email, 1, instr(email, '@') - 1)), '[^A-Z0-9]')
and REGEXP_LIKE(upper(substr(email, instr(email, '@') + 1, 1)), '[A-Z]')
and upper(email) like '%.COM';
```
```MSSQL []
/* Write your T-SQL query statement below */

with users_valid_at as (select user_id, email
from Users
where (LEN(email) - LEN(REPLACE(email, '@', ''))) = 1)
select user_id, email
from users_valid_at
where not upper(substring(email, 1, charindex('@', email) - 1)) like '[^A-Z0-9]'
and upper(substring(email, charindex('@', email) + 1, 1)) like '[A-Z]'
and upper(email) like '%.COM'
```
```Postgres []
-- Write your PostgreSQL query statement below

with users_valid_at as (select user_id, email
from Users
where (LENGTH(email) - LENGTH(REPLACE(email, '@', ''))) = 1)
select user_id, email
from users_valid_at
where not REGEXP_LIKE(upper(substr(email, 1, greatest(position('@' in email) - 1, 1))), '[^A-Z0-9]')
and REGEXP_LIKE(upper(substr(email, position('@' in email) + 1, 1)), '[A-Z]')
and upper(email) like '%.COM';
```
['MySQL', 'Oracle', 'MS SQL Server', 'PostgreSQL']