RLIKE operator in MySQL
Last Updated :
05 Dec, 2022
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.
Pattern |
What 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|p3 |
Alternation; 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_id |
First_name |
Last_name |
Joining_Date |
1 |
Sayantan |
Majumdar |
2000-01-11 |
2 |
Anushka |
Samanta |
2002-11-10 |
3 |
Sayan |
Sharma |
2005-06-11 |
4 |
Shayari |
Das |
2008-01-21 |
5 |
Sayani |
Jain |
2008-02-01 |
6 |
Tapan |
Samanta |
2010-01-11 |
7 |
Deepak |
Sharma |
2014-12-01 |
8 |
Ankana |
Jana |
2018-08-17 |
9 |
Shreya |
Ghosh |
2020-09-10 |
Now, we are going to check those employees whose last name starts with ‘S’.
SELECT * FROM Employee
WHERE Last_name RLIKE '^S' ;
Output :
Employee_id |
First_name |
Last_name |
Joining_Date |
2 |
Anushka |
Samanta |
2002-11-10 |
3 |
Sayan |
Sharma |
2005-06-11 |
6 |
Tapan |
Samanta |
2010-01-11 |
7 |
Deepak |
Sharma |
2014-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_id |
First_name |
Last_name |
Joining_Date |
4 |
Shayari |
Das |
2008-01-21 |
5 |
Sayani |
Jain |
2008-02-01 |
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...