MySQL - regexp_instr() Function

MySQL - The REGEXP_INSTR() Function

In modern data-driven applications, the ability to perform sophisticated text searches within your database is crucial. While simple pattern matching with LIKE is useful, MySQL’s regular expression capabilities offer a far more powerful and flexible way to find and validate data. Regular expressions allow you to define complex search patterns to locate or manipulate text strings.

MySQL provides a suite of functions for working with regular expressions, standardized on the powerful ICU (International Components for Unicode) library since MySQL 8.0. The REGEXP_INSTR() function is a fundamental tool in this suite, designed to find the starting position of a substring that matches a regular expression pattern.

Understanding the REGEXP_INSTR() Function

The REGEXP_INSTR() function searches a string for a regular expression pattern and returns the starting index of the match. If the pattern is not found, it returns 0. If any of the core arguments (expr or pattern) is NULL, the function returns NULL. The character indices are 1-based, meaning the first character is at position 1.

Syntax

REGEXP_INSTR(expr, pattern[, pos[, occurrence[, return_option[, match_type]]]])

Core Parameters

• expr: The input string to be searched.
• pattern: The regular expression pattern to search for.

Optional Parameters

• pos: The position in expr to begin the search. The default is 1.
• occurrence: The specific occurrence of a match to find. For example, 2 searches for the second match. The default is 1.
• return_option: Determines the position to return. 0 (default) returns the starting position of the matched substring. 1 returns the position of the character following the matched substring.
• match_type: A string of characters that modifies the matching behavior:
• c: Case-sensitive matching (default).
• i: Case-insensitive matching.
• m: Multi-line mode. ^ and $ match the start and end of lines within the string, not just the start and end of the entire string.
• n: Allows the dot (.) metacharacter to match line terminators.
• u: Unix-only line endings. Only the newline character is recognized as a line ending by ., ^, and $.

Practical Examples

Basic Search

Find the starting position of the word ‘Modern’ in a string. The search is case-sensitive by default.

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'Modern') AS Result;

The pattern ‘Modern’ is found starting at index 3.

Result
3

Case-Insensitive Search

To find ‘modern’ regardless of case, use the i match type.

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'modern', 1, 1, 0, 'i') AS Result;

This also finds the match at position 3.

Result
3

Finding the Nth Occurrence

Let’s find the starting position of the second occurrence of the letter ‘o’.

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', 'o', 1, 2) AS Result;

The first ‘o’ is in ‘Modern’ (position 5). The second is in ‘Tutorial’ (position 18).

Result
18

Using Advanced Patterns

The true power comes from complex patterns. This query finds the position of the first word that starts with ‘T’ and ends with ‘l’.

SELECT REGEXP_INSTR('A Modern MySQL Tutorial', '\\bT[a-z]+l\\b') AS Result;

Note: In SQL strings, the backslash \ is an escape character, so you must escape it (\\) to pass a literal backslash to the regex engine. \b is a word boundary.

Result
16

Handling No Match and NULLs

If a pattern is not found, the function returns 0. If an argument is NULL, it returns NULL.

SELECT
  REGEXP_INSTR('A Modern MySQL Tutorial', 'Legacy') AS NoMatch,
  REGEXP_INSTR('A Modern MySQL Tutorial', NULL) AS NullPattern;

NoMatch	NullPattern
0	NULL

Real-World Application: Data Validation

Imagine a USERS table where you need to find users whose email addresses are stored in a non-standard format. We want to find records where the EMAIL column does not look like a valid email address.

-- Sample Table Setup
CREATE TABLE USERS (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    NAME VARCHAR(100) NOT NULL,
    EMAIL VARCHAR(100)
);

INSERT INTO USERS(NAME, EMAIL) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob-at-example.org'),
('Charlie', 'charlie@example.net'),
('David', 'david@invalid');

-- Find users with emails that DON'T match a standard email pattern
-- We use REGEXP_LIKE here, which is often paired with REGEXP_INSTR
SELECT NAME, EMAIL
FROM USERS
WHERE NOT REGEXP_LIKE(EMAIL, '^[\\w.-]+@[\\w.-]+\\.([a-zA-Z]{2,})$');

This query would identify Bob and David as having potentially invalid email formats. REGEXP_INSTR could then be used to find the exact position of invalid characters if needed.

Client Program Integration (Modern Practices)

Executing SQL from an application requires secure, modern practices. Always use parameterized queries (prepared statements) to prevent SQL injection, even when the inputs seem safe. Here are updated examples for popular languages.

Syntax Overview

PHP
NodeJS
Java
Python



Modern PHP applications should use the PDO (PHP Data Objects) extension for database interaction due to its consistency across different database drivers and strong support for prepared statements.

$stmt = $pdo->prepare("SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result");
$stmt->execute(['A Modern MySQL Tutorial', 'modern']);
$result = $stmt->fetch(PDO::FETCH_ASSOC);



In Node.js, libraries like `mysql2` are standard. Using its promise-based API with `async/await` syntax leads to cleaner, more readable, and non-blocking code.

const mysql = require('mysql2/promise');
const [rows] = await connection.execute(
  "SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result",
  ['A Modern MySQL Tutorial', 'modern']
);



Modern Java applications use try-with-resources to ensure database resources (like connections and statements) are automatically closed, preventing resource leaks. Use a `PreparedStatement` for security.

String sql = "SELECT REGEXP_INSTR(?, ?, 1, 1, 0, 'i') AS Result";
try (Connection conn = ...; PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, "A Modern MySQL Tutorial");
    pstmt.setString(2, "modern");
    ResultSet rs = pstmt.executeQuery();
    // ... process results
}



In Python, the `mysql-connector-python` library is a common choice. Using a `with` statement for the connection and cursor ensures they are closed properly. Queries must be parameterized.

query = "SELECT REGEXP_INSTR(%s, %s, 1, 1, 0, 'i') AS Result"
params = ('A Modern MySQL Tutorial', 'modern')
with connection.cursor() as cursor:
    cursor.execute(query, params)
    result = cursor.fetchone()

Complete Example: Python

Python




import mysql.connector
from mysql.connector import errorcode

# --- Configuration ---
config = {
    'user': 'root',
    'password': 'password',
    'host': '127.0.0.1',
    'database': 'TUTORIALS',
}

search_string = 'A Modern MySQL Tutorial'
pattern = 'tutorial'

# --- Database Interaction ---
try:
    # Using a `with` statement ensures the connection is closed
    with mysql.connector.connect(**config) as connection:
        print("Connection established.")
        with connection.cursor(dictionary=True) as cursor:
            # Parameterized query to prevent SQL injection
            query = "SELECT REGEXP_INSTR(%s, %s, 1, 1, 0, 'i') AS Result"

            cursor.execute(query, (search_string, pattern))

            result = cursor.fetchone()

            if result:
                print(f"Search string: '{search_string}'")
                print(f"Pattern: '{pattern}' (case-insensitive)")
                print(f"Pattern found at position: {result['Result']}")
            else:
                print("No result found.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    print("Execution finished.")


Output
Following is the expected output:

Connection established.
Search string: 'A Modern MySQL Tutorial'
Pattern: 'tutorial' (case-insensitive)
Pattern found at position: 16
Execution finished.