SQL Learning
I am learning Advanced SQL as a new guy to the tech, so Iam writing these for my own understanding and for the new learners with the help of advanced AI tools. Starting with REGEXP -
REGEXP in SQL is used to search for values that match a specific pattern. It is commonly used when validating or filtering text, such as email addresses, phone numbers, usernames, or alphanumeric values. Unlike LIKE, which handles simple patterns, REGEXP can define more detailed rules. This makes it useful when the data needs to follow a particular format.
What are REGEXP and REGEXP_REPLACE in SQL?
Think of them like two helpers:
REGEXP is a detective: It finds text that follows a pattern.
REGEXP_REPLACE is a cleaner: It finds a pattern and replaces or removes it.
Small Dataset
Suppose we have a table called students:
student_id name phone email
1 Arjun 987–654–3210 arjun@gmail.com
2 Meera123 98765 43210 meera@yahoo.com
3 Alan 9123456780 alan@gmail.com
4 Riya@ 91–9876543210 riya@gmail.in
1. REGEXP: Find values matching a pattern
Example 1: Find names starting with A
SQL
SELECT *
FROM students
WHERE name REGEXP ‘^A’;
Result
student_id name
1 Arjun
3 Alan
Here:
^ means starts with
A is the letter we want
So, ‘^A’ means:
Find names that start with A.
Example 2: Find names containing only letters
SQL
SELECT *
FROM students
WHERE name REGEXP ‘^[A-Za-z]+$’;
Result
student_id name
1 Arjun
3 Alan
Pattern explanation
^ means the beginning of the value.
[A-Za-z] means any uppercase or lowercase letter.
+ means one or more letters.
$ means the end of the value.
Meera123 is not selected because it contains numbers.
Riya@ is not selected because it contains the @ symbol.
Example 3: Find Gmail addresses
SQL
SELECT *
FROM students
WHERE email REGEXP ‘@gmail\\.com$’;
Result
student_id name email
1 Arjun arjun@gmail.com
3 Alan alan@gmail.com
Here:
@gmail looks for the word @gmail.
\\. represents the dot before com.
$ means the email must end with .com.
2. REGEXP_REPLACE: Find and replace a pattern
REGEXP_REPLACE searches for a pattern inside a value and replaces it with something else.
Its basic structure is:
SQL
REGEXP_REPLACE(value, pattern, replacement)
For example:
SQL
REGEXP_REPLACE(phone, ‘[⁰-9]’, ‘’)
This means:
Get Leo’s stories in your inbox
Join Medium for free to get updates from this writer.
Find everything that is not a number and replace it with nothing.
Example 1: Remove dashes and spaces from phone numbers
SQL
SELECT
phone,
REGEXP_REPLACE(phone, ‘[^ 0-9]’, ‘’) AS clean_phone
FROM students;
Result
Original phone Clean phone
987–654–3210 9876543210
98765 43210 9876543210
9123456780 9123456780
91–9876543210 919876543210
Pattern explanation
[0–9] means any number from 0 to 9.
^ inside the square brackets means not.
[^ 0-9] therefore means anything that is not a number.
‘’ means replace it with nothing.
This removes spaces, dashes, letters, and special characters.
Example 2: Remove numbers from names
SQL
SELECT
name,
REGEXP_REPLACE(name, ‘[0–9]’, ‘’) AS clean_name
FROM students;
Result
Original name Clean name
Arjun Arjun
Meera123 Meera
Alan Alan
Riya@ Riya@
This removes the numbers from Meera123, but it does not remove @.
Example 3: Keep only letters in names
SQL
SELECT
name,
REGEXP_REPLACE(name, ‘[^A-Za-z]’, ‘’) AS clean_name
FROM students;
Result
Original name Clean name
Arjun Arjun
Meera123 Meera
Alan Alan
Riya@ Riya
Here, [^A-Za-z] means:
Find anything that is not an uppercase or lowercase letter.
Those characters are then removed.
Example 4: Hide numbers for privacy
SQL
SELECT
phone,
REGEXP_REPLACE(phone, ‘[0–9]’, ‘*’) AS hidden_phone
FROM students;
For example:
Plain Text
987–654–3210 → ***-***-****
Here, every number is replaced with *.
Main Difference
REGEXP
Used to find or filter matching values.
SQL
WHERE name REGEXP ‘^[A-Za-z]+$’
It asks:
Does this name contain only letters?
REGEXP_REPLACE
Used to change or clean matching values.
SQL
REGEXP_REPLACE(name, ‘[^A-Za-z]’, ‘’)
It says:
Remove everything that is not a letter.
Where are they mostly used?
REGEXP and REGEXP_REPLACE are commonly used for:
Checking email formats
Cleaning phone numbers
Removing unwanted special characters
Finding values containing only letters or numbers
Validating usernames
Cleaning names and addresses
Replacing private information with symbols
Finding text that starts or ends with certain characters
