Skip to content
Related Articles

Related Articles

Improve Article

RLIKE operator in MySQL

  • Difficulty Level : Medium
  • Last Updated : 16 Dec, 2020

RLIKE :
This operator in MySQL is used to performs a pattern match of a string expression against a pattern.

Syntax :

RLIKE pattern

Parameters :
This method accepts one parameter as mentioned in syntax.

  • pattern –The pattern which we want to match against an expression. Various pattern and their usage are described below.
PatternWhat the Pattern matches
*Zero or more instances of string preceding it
+One or more instances of strings preceding it
.Any single character
?Match zero or one instances of the strings preceding it.
^caret(^) matches Beginning of string
$End of string
[abc]Any character listed between the square brackets
[^abc]Any character not listed between the square brackets
[A-Z]match any upper case letter.
[a-z]match any lower case letter
[0-9]match any digit from 0 through to 9.
[[:<:]]matches the beginning of words.
[[:>:]]matches the end of words.
[:class:]matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters.
p1|p2|p3Alternation; matches any of the patterns p1, p2, or p3
{n}n instances of preceding element
{m,n}m through n instances of preceding element

Example-1 :
The following example finds the employees whose last name starts with the letter S.

CREATE TABLE Employee
(
Employee_id INT AUTO_INCREMENT,  
First_name VARCHAR(100) NOT NULL,
Last_name VARCHAR(100) NOT NULL,
Joining_Date DATE NOT NULL,
PRIMARY KEY(Employee_id )
);

Inserting some data to the Employee table :



INSERT INTO Employee
(First_name ,Last_name , Joining_Date )
VALUES
('Sayantan', 'Majumdar', '2000-01-11'),
('Anushka', 'Samanta', '2002-11-10' ),
('Sayan', 'Sharma', '2005-06-11' ),
('Shayari', 'Das', '2008-01-21' ),
('Sayani', 'Jain', '2008-02-01' ),
('Tapan', 'Samanta', '2010-01-11' ),
('Deepak', 'Sharma', '2014-12-01'  ),
('Ankana', 'Jana', '2018-08-17'),
('Shreya', 'Ghosh', '2020-09-10') ;

So, the Employee Table is as follows.

select * from Employee ;

Output :

Employee_idFirst_nameLast_nameJoining_Date
1SayantanMajumdar2000-01-11
2AnushkaSamanta2002-11-10
3SayanSharma2005-06-11
4ShayariDas2008-01-21 
5SayaniJain2008-02-01
6TapanSamanta2010-01-11
7DeepakSharma2014-12-01
8AnkanaJana2018-08-17
9ShreyaGhosh2020-09-10 

Now, we are going to check those employees whose last name stats with ‘S’.

SELECT * FROM Employee   
WHERE Last_name RLIKE '^S' ;

Output :

Employee_idFirst_nameLast_nameJoining_Date
2AnushkaSamanta2002-11-10
3SayanSharma2005-06-11
6TapanSamanta2010-01-11
7DeepakSharma2014-12-01 

Example-2 :
The following example finds the employees whose first name ends with the letter ‘i’.

SELECT * FROM Employee   
WHERE First_name RLIKE 'i$' ;

Output :

Employee_idFirst_nameLast_nameJoining_Date
4ShayariDas2008-01-21
5SayaniJain2008-02-01

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :