MySQL – RLIKE Operator
Table of content
- MySQL RLIKE Operator
- Patterns used with RLIKE
- RLIKE On Strings
- RLIKE Operator Using a Client Program
”;
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 −
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);
To search data from a MySQL database using a pattern or regexp through Node.js program, we need to execute the following “SELECT” statement using the query() function of the mysql2 library as −
sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''"; con.query(sql);
To search data from a MySQL database using a pattern or regexp through Java program, we need to execute the following “SELECT” statement using the JDBC function executeUpdate() as −
String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''"; statement.executeQuery(sql);
To search data from a MySQL database using a pattern or regexp through Python program, we need to execute the following “SELECT” statement using the execute() function of the MySQL Connector/Python as −
sql = ''SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa'''' cursorObj.execute(sql)
Example
Following are the programs −
$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
var mysql = require(''mysql2''); var con = mysql.createConnection({ host:"localhost", user:"root", password:"password" }); //Connecting to MySQL con.connect(function(err) { if (err) throw err; //console.log("Connected successfully...!"); //console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); sql = "SELECT * FROM person_tbl WHERE NAME RLIKE ''sh$''"; console.log("Select query executed successfully..!"); console.log("Table records: "); con.query(sql); con.query(sql, function(err, result){ if (err) throw err; console.log(result); }); });
Output
The output produced is as follows −
Select query executed successfully..! Table records: [ { ID: 3, NAME: ''Santosh'', AGE: 34, ADDRESS: ''Hyderabad'' }, { ID: 6, NAME: ''Ramesh'', AGE: 40, ADDRESS: ''Mumbai'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class RlikeOperator { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''"; rs = st.executeQuery(sql); System.out.println("Table records: "); while(rs.next()) { String name = rs.getString("Name"); System.out.println("Name: " + name); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Table records: Name: Santosh
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) #Creating a cursor object cursorObj = connection.cursor() rlike_operator_query = f"SELECT NAME FROM person_tbl WHERE NAME RLIKE ''^sa''" cursorObj.execute(rlike_operator_query) result = cursorObj.fetchall() print("Names that start with ''sa'':") for row in result: print(row[0]) cursorObj.close() connection.close()
Output
Following is the output of the above code −
Names that start with ''sa'': Santosh
”;