Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      NOT REGEXP Operator

      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.

      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 −
      PHPNodeJSJavaPython
      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);
      

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