MySQL – NOT REGEXP Operator


MySQL – NOT REGEXP Operator



”;


MySQL NOT REGEXP Operator

Technically, 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 character, multiple characters or words, etc.

In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching.

NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter.

Syntax

Following is the basic syntax of the MySQL NOT REGEXP operator −


expression NOT REGEXP pattern

Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator.

Examples

Let us start by 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)
);

The below INSERT statement inserts 7 records into the above created table −


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 CUSTOMERS table −


Select * from CUSTOMERS;

Following is the 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

Now, let us show the usage of NOT REGEXPM operator using several queries on this table.

Query to find all the names not starting with ”ra”


SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''^ra'';

Following is the output −









ID NAME AGE ADDRESS SALARY
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

Query to retrieve all the records whose names not ending with ”ik”


SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''ik$'';

Following is the output −








ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

Following is the query to find all the names that do not contain ”an”


SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''an'';

Following is the output −









ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.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

Example

The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL.

In the below query, the string value is NULL. Thus it gives output as NULL.


SELECT NULL NOT RLIKE ''value'';

Following is the output −




NULL NOT RLIKE ”value”
NULL

Here, the specified pattern is NULL. So, the output will be retrieved as NULL.


SELECT ''Tutorialspoint'' NOT REGEXP NULL;

Following is the output −




NULL NOT RLIKE ”value”
NULL


NOT REGEXP Operator Using a Client Program

Besides using MySQL queries to perform the NOT REGEXP operator, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To retrieve all the records present in a database that do not satisfy the specified pattern using MySQL Query through PHP program, we need to execute the “SELECT” statement using the mysqli function query() as follows −


$sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
$mysqli->query($sql);

To retrieve all the records present in a database that do not satisfy the specified pattern using MySQL Query through Node.js program, we need to execute the “SELECT” statement using the query() function of the mysql2 library as follows −


sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
con.query(sql);

To retrieve all the records present in a database that do not satisfy the specified pattern using MySQL Query through Java program, we need to execute the “SELECT” statement using the JDBC function executeUpdate() as −


String sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
statement.executeQuery(sql);

To retrieve all the records present in a database that do not satisfy the specified pattern using MySQL Query through Python program, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as −


sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^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 NOT REGEXP ''^sa''"; if($result = $mysqli->query($sql)){ printf("Table records(where the name does not start with ''sa''): 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(where the name does not start with ''sa''):
ID 1, Name John, Age 33, Address New York
ID 2, Name Ram, Age 29, Address Pune
ID 4, Name Tanya, Age 26, Address Paris
ID 5, Name Anmol, Age 28, Address Surat
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 NOT REGEXP ''^sa''";
 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: 1, NAME: ''John'', AGE: 33, ADDRESS: ''New York'' },
  { ID: 2, NAME: ''Ram'', AGE: 29, ADDRESS: ''Pune'' },
  { ID: 4, NAME: ''Tanya'', AGE: 26, ADDRESS: ''Paris'' },
  { ID: 5, NAME: ''Anmol'', AGE: 28, ADDRESS: ''Surat'' },
  { 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 regexp_not {
    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 * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("Name");
                String age = rs.getString("AGE");
                String address = rs.getString("ADDRESS");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}          

Output

The output obtained is as shown below −


Table records: 
Id: 1, Name: John, Age: 33, Address: New York
Id: 2, Name: Ram, Age: 29, Address: Pune
Id: 4, Name: Tanya, Age: 26, Address: Paris
Id: 5, Name: Anmol, Age: 28, Address: Surat
Id: 6, Name: Ramesh, Age: 40, Address: Mumbai


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()
notregexp_operator_query = f"SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(notregexp_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Persons whose name does not start with ''sa'' using NOT REGEXP:")
for row in results:
    print(row)
cursorObj.close()
connection.close()    

Output

Following is the output of the above code −


Persons whose name does not start with ''sa'' using NOT REGEXP:
(1, ''John'', 33, ''New York'')
(2, ''Ram'', 29, ''Pune'')
(4, ''Tanya'', 26, ''Paris'')
(5, ''Anmol'', 28, ''Surat'')
(6, ''Ramesh'', 40, ''Mumbai'')

Advertisements

”;

Leave a Reply

Your email address will not be published. Required fields are marked *