Regular Expressions in MySQL
MySQL supports regular expressions in MYSQL for pattern matching within strings using the REGEXP (or synonym RLIKE) operator. These expressions follow a specific syntax to define patterns for searching text data.
Key Components:
- Characters: Literal characters match themselves exactly (e.g., “a” matches the letter “a”).
- Metacharacters: These have special meanings within the expression (e.g., “.”, “^”, “$”).
- Quantifiers: Specify how many times a preceding element can be matched (e.g., “*”, “+”, “?”).
Common Metacharacters:
. : Matches any single character.
^ : Matches the beginning of the string.
$ : Matches the end of the string.
[] : Character class, matches any character within the brackets (e.g., [aeiou] matches any vowel).
[^] : Negated character class, matches any character not within the brackets.
* : Matches the preceding element zero or more times.
+ : Matches the preceding element one or more times.
? : Matches the preceding element zero or one time.
| (OR): Matches either the pattern before or after the pipe.
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
Escaping Characters:
Since the backslash (\) is used for special characters within the expression, you need to escape it (write \\) to represent a literal backslash in your pattern.
Basic Structure:
SELECT column_name
FROM table_name
WHERE column_name REGEXP ‘pattern’;
Examples :
Find all users whose first name contains “le”:
SELECT *
FROM users
WHERE first_name REGEXP ‘le’;
Find all users whose first name starts with “J”:
SELECT *
FROM users
WHERE first_name REGEXP ‘^J’;
Find all users whose last name ends with “son”:
SELECT *
FROM users
WHERE last_name REGEXP ‘son$’;
Find all users whose phone number starts with “321” or “654”:
SELECT *
FROM users
WHERE phone_number REGEXP ‘^(321|654)’;
Find all users whose first name starts with “A” and last name ends with “ez”:
SELECT *
FROM users
WHERE first_name REGEXP ‘^A’ AND last_name REGEXP ‘ez$’;
Find all users whose email starts with “j” (case insensitive):
SELECT *
FROM users
WHERE email REGEXP ‘^[jJ]’;
Find all users whose phone numbers contain “987” or “876” or “765”:
SELECT *
FROM users
WHERE phone_number
REGEXP ‘987|876|765’;
Find all users whose address starts with a digit:
SELECT *
FROM users
WHERE address REGEXP ‘^[0-9]’;
Find all users whose last name starts with “M” or “P” and ends with “ez”.
SELECT *
FROM users
WHERE last_name REGEXP ‘^(M|P).*ez$’;
Find all users whose phone numbers have a valid format (XXX-XXX-XXXX):
SELECT *
FROM users
WHERE phone_number REGEXP ‘^[0-9]{3}-[0-9]{3}-[0-9]{4}$’;
Find all users whose first names start and end with the same letter:
SELECT *
FROM users
WHERE first_name REGEXP ‘^(.).*\\1$’;
Find all users whose email addresses have a number before the “@” symbol:
SELECT *
FROM users
WHERE email REGEXP ‘[0-9]@’;
For Free, Demo classes Call: 020 7117 1500
Registration Link: SQL Training in Pune!
Find all users whose last names have at least 5 characters and end with “son” or “ez”:
SELECT *
FROM users
WHERE last_name REGEXP ‘.{5,}(son|ez)$’;
Find all users whose last names contain at least two consecutive vowels:
SELECT *
FROM users
WHERE last_name REGEXP ‘[aeiouAEIOU]{2}’;
Do watch our Channel to learn more: Click Here
Author:
Mahesh Kankrale
Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.
© Copyright 2021 | SevenMentor Pvt Ltd.