Regular expressions for email and phone validation

Regular expressions (regex) are patterns that describe exactly what valid text should look like - right characters, right order, right length - so a database can instantly flag anything that doesn't fit.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Validating Retail Customer Contact Patterns with Regex

SELECT customer_id, full_name, email
FROM customers
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

Result:

customer_id	full_name	email
C1002	Bob Tanaka	bob_tanaka@@retail.net
C1004	David Park	david.parkATstore.com

Step by step

After SELECT, list the column names you want, separated by commas.
```
SELECT customer_id, full_name, email
```

FROM tells SQL which table to pull the data from.

SELECT customer_id, full_name, email
FROM customers

WHERE keeps only the rows that match your condition.

SELECT customer_id, full_name, email
FROM customers
WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Practice

Try each one in your head first, then open the answer.

1. Find rows in the orders table where category does NOT match the alphabetic-with-spaces format pattern. Return order_id and category.

SELECT order_id, category FROM orders WHERE category !~ '^[A-Za-z][A-Za-z0-9 ]+$'

2. Find rows in the products table where brand does NOT match the alphabetic-with-spaces format pattern. Return product_id and brand.

SELECT product_id, brand FROM products WHERE brand !~ '^[A-Za-z][A-Za-z0-9 ]+$'

3. Find rows in the customers table where email does NOT match the valid email format pattern. Return customer_id and email.

SELECT customer_id, email FROM customers WHERE email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

4. Find rows in the store_sales table where channel does NOT match the alphabetic-with-spaces format pattern. Return sale_id and channel.

SELECT sale_id, channel FROM store_sales WHERE channel !~ '^[A-Za-z][A-Za-z0-9 ]+$'

5. Find rows in the returns table where return_id does NOT match the alphabetic-with-spaces format pattern. Return return_id and return_id.

SELECT return_id, return_id FROM returns WHERE return_id !~ '^[A-Za-z][A-Za-z0-9 ]+$'

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed