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.
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.
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
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’.
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’.
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
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 -
46+ registered 