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
      regexp_like() Function

      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.

      REGEXP_LIKE() Function

      MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.
      As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function.

      MySQL REGEXP_LIKE() Function

      The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string.

      Syntax

      Following is the syntax of the MySQL regexp_like() function −
      REGEXP_LIKE(expr, pattern[, match_type])
      

      Parameters

      The regexp_like() function takes following parameter values −
      • expr: The string in which search is performed
      • pattern: The pattern that is searched in the string
      • match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u).

      Example

      In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function −
      SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'To') 
      AS RESULT;
      
      The search pattern 'To' is present in the string, so it returned 1 as output.
      Result
      1
      Now, if there is no match found in the string, the result will be obtained as '0' as shown below −
      SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'Hello') 
      AS RESULT;
      
      Following is the output −
      Result
      0
      Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result −
      SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 't', 'c') 
      AS RESULT;
      
      Executing the query above will produce the following output −
      Result
      1

      Example

      If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the string parameter.
      SELECT REGEXP_LIKE(NULL, 'value') AS Result;
      
      Following is the output −
      Result
      NULL
      Here, we are passing NULL as the search pattern −
      SELECT REGEXP_LIKE('Welcome to Tutorialspoint', NULL) 
      AS Result;
      
      Executing the query above will produce the following output −
      Result
      NULL

      Example

      In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table 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 following query 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 of 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
      The following query selects records from the CUSTOMERS table where the NAME column starts with the letter 'K' −
      SELECT REGEXP_LIKE(NAME, '^K') 
      AS RESULT FROM CUSTOMERS;
      
      If there is a name that starts with letter 'K' it gives 1 as output, else 0 −
      Result
      0
      1
      1
      0
      0
      1
      0
      The following query checks whether the 'ADDRESS' column in the 'CUSTOMERS' table contains the letter 'K' (case-insensitive). If the address contains 'K' or 'k,' the result is 1; otherwise, it's 0.
      SELECT REGEXP_LIKE(ADDRESS, 'R', 'i') 
      AS RESULT FROM CUSTOMERS;
      
      As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter 'K' (case-insensitive) −
      Result
      0
      1
      1
      0
      0
      1
      0

      REGEXP_LIKE() Function Using a Client Program

      Besides using MySQL queries to perform the REGEXP_Like() function, 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 search for a string that is matched with specified pattern through PHP program, we need to execute the "SELECT" statement using the mysqli function query() as follows −
      $sql = "SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'To') AS RESULT";
      $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 REGEXP_LIKE('Welcome To Tutorialspoint!', 'To') AS RESULT";
      if($result = $mysqli->query($sql)){
          while($row = mysqli_fetch_array($result)){
              printf("Result: %d", $row['RESULT']);
          }
      }
      if($mysqli->error){
          printf("Error message: ", $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as shown below −
      Result: 1
      
      Prin