Applicants With a Valid College E-mail Address — SQL Easy Problem

Problem statement

The placement portal of Sahyadri Institute accepts only the institute's own e-mail addresses. An address is valid when:

• the part before the @ starts with a letter and contains only letters, digits, underscores _, periods . and hyphens -;
• there is exactly one @, followed by the domain sahyadri.edu and nothing else.

Return applicant_id, name and email for every applicant whose address is valid, in any order. An applicant with no address (email is NULL) is not valid.

Tables

Table: Applicant

Primary key: applicant_id.

email is the address the applicant typed, or NULL if they signed up with a phone number. Domains are always typed in small letters.

Examples

Example 1

Applicant

Output

How to solve Applicants With a Valid College E-mail Address

Validation rules like these are a regular expression: email REGEXP pattern (or REGEXP_LIKE(email, pattern)) is true when the address matches the pattern. Build it piece by piece:

• ^ anchors the match at the start, so nothing may come before the first letter;
• [a-zA-Z] is the required first letter — 2021pooja and .dev fail here;
• [a-zA-Z0-9_.-]* allows any number of the permitted characters. Inside brackets . is a literal period, and a - placed last is a literal hyphen;
• @sahyadri[.]edu is the domain, with its dot written as [.] so it means a period and not "any character" — a bare . would accept sahyadri-edu;
• $ anchors the end, rejecting sahyadri.edu.in.

@ is not in the allowed set, so a second @ can never sneak into the part before the domain. NULL addresses give a NULL match and drop out.

A more step-by-step alternative splits the address with SUBSTRING_INDEX: count the @s (CHAR_LENGTH(email) - CHAR_LENGTH(REPLACE(email, '@', '')) must be 1), compare the part after it with 'sahyadri.edu', and check the part before it with a smaller, anchored pattern. It is longer but easier to debug one rule at a time. Either way each address is scanned a constant number of times, so the query is linear in the total length of the addresses.

Reference solution (MySQL)

SELECT applicant_id, name, email
FROM Applicant
WHERE email REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*@sahyadri[.]edu$'

Another way

SELECT applicant_id, name, email FROM Applicant WHERE REGEXP_LIKE(email, '^[a-zA-Z][a-zA-Z0-9_.-]*@sahyadri[.]edu$')

Another way

SELECT applicant_id, name, email FROM Applicant
WHERE CHAR_LENGTH(email) - CHAR_LENGTH(REPLACE(email, '@', '')) = 1
  AND SUBSTRING_INDEX(email, '@', -1) = 'sahyadri.edu'
  AND SUBSTRING_INDEX(email, '@', 1) REGEXP '^[a-zA-Z][a-zA-Z0-9_.-]*$'