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_substr() 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_SUBSTR() Function

      Regular expressions in MySQL are used in search operations to filter and retrieve records from a database table that match specified patterns.
      This process of detecting patterns in a set of data is known as pattern matching. It is helpful whenever the data is considered to have similar characteristics; in such cases, you might locate a pattern in the data and all its occurrences. Pattern matching is usually performed on raw data to make sure it is syntactically correct.
      Similarly, in MySQL, pattern matching is performed to check whether the data is correctly stored or not. If not, the incorrect pattern is detected (and sometimes replaced) using functions of regular expressions. The regexp_substr() function is used to detect specified patterns from a set of data.

      MySQL REGEXP_SUBSTR() Function

      The MySQL regexp_substr() function is used for pattern matching in a database. This function returns the substring of a string that matches the pattern specified, NULL if either there is no match or, the string or the pattern is NULL. Here, a pattern is defined as an extended regular expression or just an ordinary string.

      Syntax

      Following is the syntax of the MySQL regexp_substr() function −
      REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]])
      

      Parameters

      The regexp_substr() function takes following parameter values −
      • expr: The string in which search is performed
      • pattern: The pattern that is searched in the string
      This method also accepts following optional arguments −
      • pos: Starting position of the search
      • occurrence: Which occurrence of a match to replace. If omitted, the default is 1 so it retrieves the first occurrence only.
      • match_type: A string that specifies how to perform matching.

      Example

      Following example shows the usage of MySQL regexp_substr() function on a simple string 'Welcome To Tutorialspoint!' using the SELECT statement as follows −
      SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT;
      
      On executing the given query, the output is displayed as follows −
      Result
      We
      If the pattern is not present in the string, the result is returned as NULL. Let us try to search for the pattern 'Hi' in the same string 'Welcome To Tutorialspoint!' using the following query −
      SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'Hi') AS RESULT;
      
      Following is the output −
      Result
      NULL

      Example

      Let us pass 5 as value to the 'pos' parameter so the search starts from the 5th position in the given string; and as we are passing the occurrence value as 2, the second occurrence of the pattern 'to' after 5th position will be retrieved, irrespective of its case −
      SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'To', 5, 2, 'i')
      AS RESULT;
      

      Output

      When we execute the above query, the output is obtained as follows −
      Result
      to

      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 expression parameter.
      SELECT REGEXP_SUBSTR(NULL, 'value');
      
      On executing the given program, the output is displayed as follows −
      REGEXP_SUBSTR(NULL, 'value')
      NULL
      Here, we are passing NULL as a pattern to search −
      SELECT REGEXP_SUBSTR('Welcome to Tutorialspoint', NULL)
      AS Result;
      
      Following is the output −
      Result
      NULL

      Example

      In the following query, we are performing a search operation on a database table named CUSTOMERS using the REGEXP_SUBSTR() function. Firstly, let us create the table using the query below −
      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)
      );
      
      Following query adds 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
      Now, we are using the REGEXP_SUBSTR() function to retrieve the substring from the the NAME column values that begin with 'Ra'.
      SELECT REGEXP_SUBSTR(NAME, '^Ra') AS RESULT FROM CUSTOMERS;
      

      Output

      As we can see the output below, only the first record in NAME column has a substring 'Ra' in it −
      Result
      Ra
      NULL
      NULL
      NULL
      NULL
      NULL
      NULL

      REGEXP_SUBSTR() Funcion Using a Client Program

      We can also perform the MySQL REGEXP_SUBSTR() function using the client programs to detect specified patterns from a set of data.

      Syntax

      Following are the syntaxes of this operation in various programming languages −
      PHPNodeJSJavaPython
      To retrieve a part of a string that matches a specific pattern from MySQL database through PHP program, we need to execute the 'SELECT' statement using the mysqli function query() as follows −
      $sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') 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_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
      if($result = $mysqli->query($sql)){
          while($row = mysqli_fetch_array($result)){
              printf("Result: %s", $row['RESULT']);
          }
      }
      if($mysqli->error){
          printf("Error message: ", $mysqli->error);
      }
      $mysqli->close();
      

      Output

      The output obtained is as shown below −
      Result: We
      
      P