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_instr() 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_INSTR() 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.
      A regular expression is technically 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.
      These regular expressions in MySQL provide various functions and operators to easily perform the search operations. One such function is regexp_instr() function.

      MySQL REGEXP_INSTR() Function

      The MySQL regexp_instr() function is used to match specified patterns with either a string or the data in database tables. This function returns the starting index of the substring of a string that matches the specified pattern, returns 0 if there is no match, or NULL if the string or the pattern is NULL. Character indices of this string starts at 1.

      Syntax

      Following is the syntax of the MySQL regexp_instr() function −
      REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])
      
      Where expr is the string in which the search is to be performed and pat is the pattern/regular expression that is to be searched. In addition to the expression and string values this method accepts the following optional parameters.

      Parameters

      The regexp_instr() function takes following parameter values −
      • expr: The string in which search is performed
      • pattern: The pattern that is searched in the string
      Following are the optional arguments that can be passed to this function −
      • pos: The position in expr at which to start the search. If omitted, the default is 1.
      • occurrence: Which occurrence of a match to search for. If omitted, the default is 1.
      • return_option: Which type of position to return. If this value is 0, REGEXP_INSTR() returns the position of the matched substring's first character. If this value is 1, REGEXP_INSTR() returns the position following the matched substring. If omitted, the default is 0.
      • match_type:This is a string which consists of various characters representing the desired features of the match this may contain one or all of these characters. Following are various characters using which you can specify the match type.

      Example

      In this example, we are performing a search operation on a simple string using the MySQL REGEXP_INSTR() function −
      SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT;
      
      The pattern 'To' is found at 9th index −
      Result
      9
      If there is no match found in the string, the return value will be '0' −
      SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'xx') AS RESULT;
      
      Following is the output −
      Result
      0

      Example

      Let us also pass optional arguments to this function and observe the result. Here, the search search position starts at 5 to find the 2nd occurrence of 'T' after that position. As the return option is set to 1, the position following the match is returned. −
      SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'T', 5, 2, 1) AS RESULT;
      

      Output

      Following is the output −
      Result
      13

      Example

      The following query searches for the position for any alphabetic character in the provided string '9848032919'. If found, it returns 1. Else, 0.
      SELECT REGEXP_INSTR('9848032919', '[[:alpha:]]');
      

      Output

      Executing the query above will produce the following output −
      REGEXP_INSTR('9848032919', '[[:alpha:]]')
      0

      Example

      The below query searches for the position of either 'town' or 'city' in the provided string −
      SELECT REGEXP_INSTR('Vishakapatnam is city of destiny ', 'town|city')
      As Result;
      

      Output

      The output for the above query is produced as given below −
      Result
      0

      Example

      If either of the first two arguments passed to this function is NULL, this function returns NULL. Here, we are passing 'NULL' as search pattern.
      SELECT REGEXP_INSTR('Tutorialspoint', NULL) 
      As Result;
      
      If we compile and run the query, the result is produced as follows −
      Result
      NULL
      In the following query, we are passing 'NULL' to the string parameter.
      SELECT REGEXP_INSTR(NULL, 'to') 
      As Result;
      
      When we execute the query above, the output is obtained as follows −
      Result
      NULL

      Example

      In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_INSTR() 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)
      );
      
      Insert some records into the above created table using the following INSERT query −
      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 below 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
      The following query selects the position of the first occurrence of a string that starts with the letter 'K' from the NAME column in the CUSTOMERS table −
      SELECT REGEXP_INSTR(NAME, '^K') 
      AS RESULT FROM CUSTOMERS;
      
      As we can see in the output below, there are three string in NAME column that starts with letter K.
      Result
      0
      1
      1
      0
      0
      1
      0

      Client Program

      We can also perform the MySQL REGEXP_INSTR() function using the client programs (such as PHP, Node.js, Java, Python) to match specified pattern with either a string or the data in database tables.

      Syntax

      Following are the syntaxes of this operation in various programming languages −
      PHPNodeJSJavaPython
      To retrieve all the records from a MySQL database that match a specific pattern, whether it's a string or data, through a PHP program, we execute the 'SELECT' statement using the mysqli function query() as follows −
      $sql = "SELECT REGEXP_INSTR('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_INSTR('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 follows −
      Result: 9      
      
      P