MySQL - NOT REGEXP Operator

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)

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')