MySQL - RLIKE Operator

Hello, aspiring database enthusiasts! Today, we're going to embark on an exciting journey into the world of MySQL's RLIKE operator. As your friendly neighborhood computer science teacher, I'm here to guide you through this topic with plenty of examples and explanations. So, grab your virtual notepads, and let's dive in!

MySQL RLIKE Operator

What is RLIKE?

RLIKE is a powerful operator in MySQL that allows us to perform pattern matching using regular expressions. Now, I know what you're thinking - "Regular expressions? That sounds scary!" But don't worry, we'll break it down step by step.

Think of RLIKE as a super-smart search tool. It's like having a detective in your database who can find information based on complex patterns. Cool, right?

Basic Syntax

The basic syntax of the RLIKE operator is:

expr RLIKE pattern

Here, expr is the expression (usually a column name) that we want to search in, and pattern is the regular expression we want to match.

Let's start with a simple example:

SELECT * FROM customers WHERE last_name RLIKE 'son$';

This query will find all customers whose last name ends with 'son'. The $ symbol in the pattern means "end of the string". So, it will match names like Johnson, Wilson, or Anderson.

Patterns used with RLIKE

Now, let's look at some common patterns we can use with RLIKE. I like to think of these as the secret codes our database detective uses to solve mysteries!

Pattern	Description	Example
^	Matches the beginning of a string	'^A' matches 'Apple' but not 'Banana'
$	Matches the end of a string	'e$' matches 'Apple' but not 'Banana'
.	Matches any single character	'b.t' matches 'bat', 'bit', 'but', etc.
*	Matches zero or more occurrences of the previous character	'go*gle' matches 'ggle', 'google', 'gooogle', etc.
+	Matches one or more occurrences of the previous character	'go+gle' matches 'google', 'gooogle' but not 'ggle'
[]	Matches any single character within the brackets	'[aeiou]' matches any vowel
[^]	Matches any single character not within the brackets	'[^0-9]' matches any non-digit

Let's put some of these to use!

SELECT * FROM products WHERE name RLIKE '^A.*e$';

This query finds all products whose names start with 'A' and end with 'e'. The .* in the middle means "zero or more of any character". So this would match names like "Apple", "Airplane", or even just "Ae".

RLIKE On Strings

RLIKE isn't just for searching in tables. We can use it directly on strings too! This is great for testing our patterns before using them in a query.

SELECT 'MySQL' RLIKE 'SQL$';

This will return 1 (true) because 'MySQL' indeed ends with 'SQL'.

Here's a fun one:

SELECT 'Mississippi' RLIKE 's+is+';

This returns 1 because 'Mississippi' contains one or more 's' followed by 'i' followed by one or more 's'. It matches 'ssis' in the middle of the word.

RLIKE Operator Using a Client Program

Now, let's see how we can use RLIKE in a more complex scenario. Imagine we have a table of email addresses, and we want to find all Gmail addresses. Here's how we could do that:

SELECT * FROM users WHERE email RLIKE '^[A-Za-z0-9._%+-]+@gmail\\.com$';

Wow, that looks complicated! Let's break it down:

• ^ : Start of the string
• [A-Za-z0-9._%+-]+ : One or more letters, numbers, or special characters allowed in email addresses
• @ : The @ symbol
• gmail\\.com : Literally "gmail.com" (we need to escape the dot with a backslash)
• $ : End of the string

This pattern will match any standard Gmail address.

Here's a little story from my teaching experience: I once had a student who was trying to find all users with AOL email addresses in a database. He was manually checking each entry (there were thousands!). When I showed him how to do it with RLIKE, he was so excited he practically danced around the classroom!

Practice Time!

Let's end with a fun exercise. Can you write a RLIKE pattern to find all phone numbers in the format (XXX) XXX-XXXX? Give it a try, and then check the answer below!

SELECT * FROM contacts WHERE phone RLIKE '^\\\([0-9]{3}\\\) [0-9]{3}-[0-9]{4}$';

This pattern matches:

• ^\\\( : Start with an open parenthesis (escaped twice because it's special in both regex and MySQL)
• [0-9]{3} : Exactly three digits
• \\\) : A closing parenthesis
• ` ` : A space
• [0-9]{3} : Three more digits
• - : A hyphen
• [0-9]{4} : Four final digits
• $ : End of the string

And there you have it! You're now equipped with the power of RLIKE. Remember, like any superpower, use it wisely. Happy querying, future database wizards!

Credits: Image by storyset