MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

RLIKE Operator
MySQL RLIKE Operator
The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise.
A regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single/multiple characters or words, etc.
The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria.
Syntax
Following is the basic syntax of the RLIKE operator in MySQL −
expression RLIKE pattern
﻿
Patterns used with RLIKE
RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator.
Pattern
What the pattern matches
^
Beginning of string
$
End of string
*
Zero or more instances of preceding element
+
One or more instances of preceding element
{n}
n instances of preceding element
{m,n}
m through n instances of preceding element
.
Any single character
[...]
Any character listed between the square brackets
[^...]
Any character not listed between the square brackets
[A-Z]
Any uppercase letter
[a-z]
Any lowercase letter
[0-9]
Any digit (from 0 to 9)
[[:<:]]
Beginning of words
[[:>:]]
Ending of words
[:class:]
A character class, i.e. use [:alpha:] to match letters from the alphabet
p1|p2|p3
Alternation; matches any of the patterns p1, p2, or p3
Example
The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
﻿
Now, insert some values into the above created table using the INSERT statements given below −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
﻿
Execute the following query to display all the records present in the CUSTOMERS table −
SELECT * FROM CUSTOMERS;
﻿
Following are the records present in CUSTOMERS table −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
RLIKE with Patterns −
In the following query, we are finding all the records from CUSTOMERS table whose name starts with 'ch' −
SELECT * FROM CUSTOMERS WHERE NAME RLIKE '^ch';
﻿
Executing the query above will produce the following output −
ID
NAME
AGE
ADDRESS
SALARY
4
Chaitali
25
Mumbai
6500.00
The following query displays all the records whose names ends with 'sh' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'sh$';
﻿
Following are records whose name ends with 'sh' −
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
Here, we are retrieving the records that have names containing 'an' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'an';
﻿
Following are the records −
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
This following query retrieves all the records whose names are ending with an vowel −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '[aeiou]$';
﻿
Following are the records −
ID
NAME
AGE
ADDRESS
SALARY
4
Chaitali
25
Mumbai
6500.00
The below query finds all the names starting with a consonant and ending with 'ya' −
SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '^[^aeiou].*ya$';
﻿
As we observe the output, there are no records that starts with consonant and ends with 'ya'.
Empty set (0.00 sec)
﻿
RLIKE On Strings
The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string, or 0 if it doesn't. The result is retrieved as a result-set using the SQL SELECT statement.
Syntax
Following is the basic syntax of the RLIKE operator in MySQL −
SELECT expression RLIKE pattern;
﻿
Example
In the following example, we are using the RLIKE query to check if a pattern exists in an individual string or not −
SELECT 'Welcome To Tutorialspoint!' RLIKE 'To';
﻿
The result-set will contain 1 because the pattern 'TO' exists in the specifed string.
'Welcome To Tutorialspoint!' RLIKE 'To'
1
Here, the pattern 'Hello' does not exist in the specifed string, thus it returns 0 as output.
SELECT 'Welcome To Tutorialspoint!' RLIKE 'Hello';
﻿
Executing the query above will produce the following output −
'Welcome To Tutorialspoint!' RLIKE 'Hello'
0
Example
REGEXP is alternative syntax to the RLIKE in MySQL. Both the operators have same result.
In the below query, if the given pattern is not found in the specifed string, this operator returns 0 −
SELECT 'Welcome to Tutorialspoint' REGEXP 'unknown';
﻿
Following is the output −
'Welcome to Tutorialspoint' REGEXP 'unknown'
0
Here, the pattern 'is' does not exist in the specifed string, thus it returns 1 as output.
SELECT 'This is a sample string' REGEXP 'is';
﻿
Executing the query above will produce the following output −
'This is a sample string' REGEXP 'is'
1
Example
If either of the first two operands is NULL, the RLIKE operator returns NULL.
SELECT NULL RLIKE 'value';
﻿
Following is the output −
NULL RLIKE 'value'
NULL
Here, the pattern we are searching is NULL, thus the output will also be NULL.
SELECT 'Tutorialspoint' RLIKE NULL;
﻿
Executing the query above will produce the following output −
'Tutorialspoint' RLIKE NULL
NULL
Example
If you use the NOT clause before RLIKE operator, it returns 0 in case of a match else returns 1 (reverse of the original return values).
SELECT NOT 'This is a sample string' RLIKE 'is';
﻿
Following is the output −
NOT 'This is a sample string' RLIKE 'is'
0
Here, the pattern 'unknown' is not present in the specifed string, thus the following query returns 1 as output.
SELECT NOT 'Welcome to Tutorialspoint' REGEXP 'unknown';
﻿
Executing the query above will produce the following output −
NOT 'Welcome to Tutorialspoint' REGEXP 'unknown'
1
RLIKE Operator Using a Client Program
We can also perform the MySQL RLike operator using the client programs to search data in a database using patterns (or regular expressions).
Syntax
Following are the syntaxes of this operation in various programming languages −
PHPNodeJSJavaPython
To search data from a MySQL database using a pattern or regexp through PHP program, we need to execute the following "SELECT" statement using the mysqli function query() as −
$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE 'sh$'";
$mysqli->query($sql);
﻿
Example
Following are the programs −
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE 'sh$'";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id %d, Name %s, Age %d, Address %s",
        $row['ID'],
        $row['NAME'],
        $row['AGE'],
        $row['ADDRESS']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
﻿
Output
The output obtained is as follows −
Table records:
Id 3, Name Santosh, Age 34, Address Hyderabad
Id 6, Name Ramesh, Age 40, Address Mumbai
﻿
﻿
﻿
﻿
﻿
﻿
﻿

Pattern	What the pattern matches
^	Beginning of string
$	End of string
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{n}	n instances of preceding element
{m,n}	m through n instances of preceding element
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
[A-Z]	Any uppercase letter
[a-z]	Any lowercase letter
[0-9]	Any digit (from 0 to 9)
[[:<:]]	Beginning of words
[[:>:]]	Ending of words
[:class:]	A character class, i.e. use [:alpha:] to match letters from the alphabet
p1\|p2\|p3	Alternation; matches any of the patterns p1, p2, or p3

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

RLIKE Operator

MySQL RLIKE Operator

Syntax

Patterns used with RLIKE

Example

RLIKE On Strings

Syntax

Example

Example

Example

Example

RLIKE Operator Using a Client Program

Syntax

Example

Output