Employee Email Verification

The engine walks each email string left-to-right against an anchored regular expression: ^ pins the match to the first character and $ to the last, so the pattern must consume the entire string or the whole row is rejected — there is no "contains a valid-looking part" loophole. Validity is therefore a single boolean: does the full string fit the grammar letter, then any run of allowed username characters, then the literal domain?

Problem

Table Employees(employee_id PK, name, email). Return the rows whose email is valid, in any order. A valid email is a username followed by a fixed domain:

• Username — starts with a letter, then any mix of letters, digits, underscore _, period ., hyphen -.
• Domain — the literal @corpexample.com.

Spec note (corrected here). The original prose said "the domain is @corpexample" but every example and the regex require the full @corpexample.com — Charlie's charlie@corpexample.net is rejected precisely because its domain is not .com. The two statements contradicted each other. The real contract is @corpexample.com; the bare @corpexample wording was a carry-over error and is wrong.

The query (correct version)

SELECT *
FROM   Employees
WHERE  email REGEXP '^[A-Za-z][A-Za-z0-9_.-]*@corpexample\.com$';

Per-token breakdown — read it as one left-to-right machine:

Note . and - need no backslash inside the class: . is already literal there, and - is literal when it sits last. Only the dot in \.com — outside the class — must be escaped.

Worked trace on the sample rows

The engine evaluates each string against the anchored pattern. Below, the cursor either consumes the whole string (PASS) or hits a character the pattern cannot accept (FAIL).

Output: rows 101 (Alice), 102 (Bob), 104 (Dave) — exactly the three that fully consumed the pattern.

Why the naive version is wrong

A common first attempt skips one or both anchors and uses an unescaped dot:

-- WRONG: no anchors, literal-looking but unescaped dot
WHERE email REGEXP '[A-Za-z][A-Za-z0-9_.-]*@corpexample.com'

Two silent bugs:

• No ^…$. REGEXP tests for a match anywhere in the string, so !!!alice@corpexample.com??? passes — the engine just finds the good substring in the middle. Injection-style junk sails through.
• Unescaped . before com means "any character," so x@corpexampleZcom matches. You wanted a literal dot; you got a wildcard.

The fix is the anchored, escaped form shown above. The lesson generalizes: a validator must be anchored, or it is a substring search wearing a validator's coat.

Portability — the operator is not the same across engines

The pattern is standard POSIX-ish regex, but the syntax to invoke it differs, and this trips people moving a query between databases:

The grader's earlier inline ASCII tables hid this entirely; on a real migration it is the first thing to break.

Pitfalls

• Forgetting the anchors. The single most common bug: an unanchored REGEXP is a containment test, not a validator. Always wrap with ^…$.
• Over-escaping inside the class. Writing [A-Za-z0-9_\.\-] works in MySQL but is needless noise — inside [] the dot is already literal and a trailing hyphen needs no backslash. In Postgres' ~ the extra backslashes can even change meaning depending on standard-conforming-strings settings.
• Backslash doubling. The original page wrote \\.com (double backslash) inside a single-quoted SQL literal. Whether that is one literal dot or a literal backslash-then-any-char depends on how the client processes escapes; the unambiguous, portable form is a single \.. Test the actual reject rows, do not eyeball the regex.
• Hyphen placement. A hyphen in the middle of a class ([a-.-]) becomes a range and silently changes what matches. Keep - first or last.
• Case and collation surprises. Relying on MySQL's default case-insensitivity to accept uppercase letters is fragile across collations; encode both cases explicitly as [A-Za-z].
• Trusting regex for full RFC email validation. This pattern validates this company's narrow rule, not real-world email. Real RFC 5321/5322 addresses (quoted locals, IP-literal domains) are far larger than any interview regex; don't reuse this as a general email validator.

Takeaways

• A validator is an anchored, full-string match: ^…$ turns "contains" into "is."
• Escape the dot you mean literally (\.); inside a character class the dot is already literal and the hyphen only needs care about position.
• The regex pattern is portable; the operator is not — MySQL REGEXP, Postgres ~, SQLite needs an extension, SQL Server has none.
• The true spec here is @corpexample.com; the .net row failing is the tell that distinguishes the real rule from the buggy "@corpexample" prose.

Based on the LeetCode-style "Find the Valid Emails" / employee-email-verification problem and the MySQL REGEXP, PostgreSQL ~, and SQLite regex documentation. Anchoring and character-class behaviour follow the POSIX ERE / PCRE references (Friedl, Mastering Regular Expressions). Re-authored and deepened for this guide: resolved the @corpexample vs @corpexample.com spec contradiction in favour of the example data, added a per-token breakdown, a state-machine diagram, a "why the naive version is wrong" note, and the cross-engine portability table.