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

• c This character indicates the case-sensitive matching.

• i This character indicates the case-insensitive matching.

• m This character indicates the i.e., multiple lines (separated by line terminators) with in a single string are recognized.

• n If this character is specified, the dot (.) character matches line terminators.

• u If this character is specified, only new line character is recognized as line ending by ., ^, and $.

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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 −

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.

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 −

$sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT" 
cursorObj.execute(sql)

Example

Following are the programs −

$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();

Result: 9      

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 REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
 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);
 });
});    

Select query executed successfully..!
Table records:
[ { RESULT: 9 } ]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_instr {
    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 REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT";
            rs = st.executeQuery(sql);
                  while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Result: 9

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()
regexp_instr_query = f"SELECT REGEXP_INSTR('Welcome To Tutorialspoint!', 'To') AS RESULT"
cursorObj.execute(regexp_instr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_INSTR() Function:")
for row in results:
    position = row[0]
    if position > 0:
        print(f"The pattern 'To' found at position {position}")
    else:
        print("The pattern 'To' not found in the given string")
cursorObj.close()
connection.close()    

Result of REGEXP_INSTR() Function:
The pattern 'To' found at position 9