1
Email Validation: Use REGEXP to check basic structure like username, @ symbol, domain, and TLD.
2
Phone Number Validation: REGEXP can validate digit counts, optional country codes, separators, and formats.
3
Flexibility: REGEXP enables enforcing input patterns without requiring external validation logic.

Validate Email Address

Validate Phone Number (10-digit, India example)

Validate Phone with Optional Country Code (+91)

Validate Phone with Hyphens or Spaces

Difficulty: 5/10

Topics: REGEXP syntax, validation constraints, performance considerations

0-2 years experience

1
Write a MySQL query that selects rows where the email column matches a simple pattern like 'something@domain.com' using REGEXP.
2
How would you add a CHECK constraint to a users table to ensure phone numbers are exactly 10 digits using REGEXP?
3
If you run SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$', which rows are filtered out and why?

2-5 years experience

1
Your team notices some stored email addresses contain spaces and uppercase letters, causing validation to fail. How would you modify the REGEXP or query to handle these cases without altering existing data?
2
During a migration, a new requirement adds international phone numbers with an optional '+' and country code. Design a MySQL REGEXP to validate both local and international formats and discuss the trade‑offs.
3
You added a REGEXP CHECK on the phone column, but inserts now fail for numbers that include parentheses. How would you debug and fix the pattern?

5-8 years experience

1
Explain the performance impact of using REGEXP on a table with millions of rows. What indexing strategies or schema changes could you use to keep validation efficient?
2
Your service needs to enforce email uniqueness and format validation at the database level. How would you combine a UNIQUE index with a REGEXP CHECK, and what pitfalls might arise under high write concurrency?
3
If validation rules (e.g., new TLDs) must evolve without downtime, how would you architect the MySQL schema and deployment process to allow safe updates to the REGEXP patterns?

8+ years experience

1
Across multiple microservices, some validate emails in code while others rely on MySQL REGEXP constraints. How would you establish a consistent validation strategy, and what are the pros and cons of moving validation entirely to the database?
2
Your organization plans to migrate from MySQL 5.7 to 8.0, which introduces native CHECK constraints and an improved regex engine. How would you redesign the validation layer to leverage these features while minimizing risk to existing applications?
3
Consider a legacy system where phone numbers are stored in free‑text columns. Propose a phased approach to refactor the schema, enforce REGEXP validation, and migrate existing dirty data, balancing data integrity and service availability.