Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction 
2.
Regular Expression
3.
RLIKE Operator in MySQL
3.1.
Syntax
3.2.
Return Value
3.3.
Example 1
3.4.
Output
3.5.
Output
3.6.
Example 2
3.7.
Output
4.
Use of RLIKE
5.
LIKE VS RLIKE
5.1.
Example
6.
Frequently Asked Questions
6.1.
What is the use of the RLIKE operator in MySQL?
6.2.
Why is it called a regular expression?
6.3.
What distinguishes MySQL from SQL?
7.
Conclusion
Last Updated: Mar 27, 2024
Medium

RLIKE Operator in MySQL

Author Shiva
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction 

Hello, readers. Have you ever felt the need to extract a particular kind of data set from the Database? Data that follows specific patterns can be extracted from the database using the RLIKE operator in MySQL. This is done by specifying something called regular expression.

In this article, we will briefly cover regular expression then we will explore the RLIKE operator in MySQL.

introductory image

Regular Expression

A regular expression is also known as a rational expression. A regular expression is a string that is used to define the search pattern.

The regular expression is not limited to MySQL only. Every programming language, including popular ones like C++, Java, and Python, uses it.

We will use the regular expressions in the section below. 

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

RLIKE Operator in MySQL

The MySQL RLIKE operator compares a string expression to a pattern in order to find matches. This pattern that we define is the regular expression.
The following describes many patterns and their description.

(asterisk)

It has 0 or more instances of the character before it.

(plus sign)

A single or several instances of the strings before it.

(dot)

Any single character.

(question mark)

Match 0 or 1 instance of the strings that came before it.

(caret)

Caret(^) matches the start of the string.

(dollar sign)

End of string.

[abc]

Any character is enclosed in square brackets.

[^abc]

Any character not specified within square brackets.

[A-Z]

Matches any uppercase letter.

[a-z]

Matches any lowercase letter.

[0-9]

Matches any digit from 0 - 9.

[[:<:]]

Matches the word's beginning.

[[:>:]]

Matches the end of the words.

[:class:]

Matches a character class, such as [:alpha:] for letters, [:space:] for spaces, [:punct:] for punctuations, and [:upper:] for letters of the upper class..

pat1|pat2|pat3

Matches any of the patterns pat1, pat2, or pat3.

{n}

n occurrences of the previous element.

{m,n}

Instances of the prior element in the range of m to n.

Syntax

This method accepts the one parameter specified in the syntax.

RLIKE pattern

 

pattern: This is the regular expression to determine a specific pattern. 
Here’s a regular expression to check email addresses: 
^([a-zA-Z0-9_\-\.]+)@([a-zA-Z0-9_\-\.]+)\.([a-zA-Z]{2,5})$

Return Value

The query will return the entries with the features as described in the regular expression. 

Example 1

Let’s create a table and perform some queries in it using the RLIKE operator in MySQL.

CREATE TABLE Demo(
	id INT AUTO_INCREMENT,  
	name VARCHAR(100) NOT NULL,
	date DATE NOT NULL,
	PRIMARY KEY(id)
);


INSERT INTO Demo
(name, date )
VALUES
('Samantha', '2000-01-01'),
('Aruna',  '2001-11-20' ),
('Sanju',  '2002-06-21' ),
('Saya', '2003-01-21' ),
('Sanjay', '2004-08-11' ),
('Tanu',  '2005-03-21' ),
('Dev', '2006-11-21'  ),
('Ankara', '2016-01-17'),
('Shantanu', '2011-02-10') ;


select * from Demo;

Output

output

These are all the entries available in the table. 

Now, The query to extract entries of people whose name starts with “A” using the caret(^) symbol:

select * from Demo
where name RLIKE '^A';


The pattern is defined inside single quotes.

Output

List of people whose name starts with ‘a’.

output

Example 2

This query will extract entries where the name ends with ‘a’.

select * from Demo
where name RLIKE 'a$';

Output

Here is the list of all the entries where the name ends with ‘a’.

output

Use of RLIKE

In MySQL, pattern matching is accomplished using the RLIKE operator. The given strings are checked to see if they match a regular expression. It gives a result of 1 if the strings match the regular expression and a result of 0 otherwise.

LIKE VS RLIKE

Consider LIKE as C and RLIKE as C++. It has all the goodies that LIKE provides, buts it’s more. You can do all the operations using RLIKE that are achieved by LIKE, but vice-versa is not true. 

The similarity between LIKE and RLIKE is that both of them can perform pattern searching. 

And the difference is that RLIKE uses regular expressions, and that creates the possibility of doing much more than just string searches. 

The SQL LIKE operator compares a value to other values that are similar to it by using wildcard operators. The LIKE operator is used in conjunction with two wildcards, ‘%’ and ‘_’.

Example

CREATE TABLE Demo(
	id INT AUTO_INCREMENT, 
	firstName VARCHAR(100) NOT NULL,
	lastName VARCHAR(100),
	PRIMARY KEY(id)
);
INSERT INTO Demo
(firstName, lastName )
VALUES
('Samantha', 'Raw'),
('Aruna',  'Bondur' ),
('Chirag',  'Deva' ),
('Saya', 'Sitha' ),
('Sanjay', 'Tiwari' ),
('Tanu',  'Grover' ),
('Dev', 'Prayag'  ),
('Ankara', 'Chintagiri'),
('Shantanu', 'Dulgaj') ;
SELECT 
    firstName, lastName
FROM
    Demo
WHERE
    firstName LIKE 'S%';

Output

output

 

Frequently Asked Questions

What is the use of the RLIKE operator in MySQL?

The RLIKE operator is used for matching strings of a specific pattern. This is done via regular expression.

Why is it called a regular expression?

The phrase "regular expression" derives from theories in mathematics and computer science and refers to the regularity of mathematical expressions.

What distinguishes MySQL from SQL?

The main purposes of SQL are to query and manage database systems. MySQL gives you the ability to manage, save, edit, and delete data while also storing it in an organized manner. The MySQL Workbench is a built-in tool that makes it easier to design, build, and create databases.

Conclusion

In this article, we learned about the RLIKE operator in MySQL, with some examples. 

If you want to explore more, here are some related articles - 

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSACompetitive ProgrammingSystem Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninjas!

Live masterclass