Find Index of Substring in MySQL

In MySQL, you can use the INSTR() and LOCATE() functions to locate the starting position of a substring within a string.

Finding index of a specific substring is useful for parsing text, validating data, and performing searches on specific patterns within fields.

Using INSTR() and LOCATE() Functions to Find Substring Index

Both INSTR() and LOCATE() are MySQL functions that allow you to find the position of a substring within a string. Each function returns the position of the first occurrence of the substring, starting from 1 (indicating the first character of the string). If the substring is not found, they return 0.

INSTR() Syntax

</>
Copy
INSTR(string, substring)

The INSTR() function returns the index of the first occurrence of substring within string.

LOCATE() Syntax

</>
Copy
LOCATE(substring, string, [start_position])

Similarly, LOCATE() returns the index of the first occurrence of substring within string. It also allows for an optional start_position to specify where the search begins.

Setting Up Example Table

For this tutorial, we will use a books table with a title column to demonstrate how to find substring positions.

1. First, create a new database called library_db:

</>
Copy
CREATE DATABASE library_db;

2. Use the library_db database:

</>
Copy
USE library_db;

3. Create the books table with an id and title column:

</>
Copy
CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255)
);

4. Insert sample data into the books table:

</>
Copy
INSERT INTO books (title)
VALUES ('Introduction to SQL'),
       ('Advanced MySQL Techniques'),
       ('SQL Server Basics');

With this setup complete, we can use INSTR() and LOCATE() to find substring positions within book titles.

Find Index of Substring in MySQL - Setup for examples

Examples: Finding Substring Positions in MySQL

Let’s explore examples to find specific substrings within the title column of books table.

books table

1 Using INSTR() to Find a Word in a Title

To find the position of the word “SQL” in each book title:

</>
Copy
SELECT title, INSTR(title, 'SQL') AS position_of_sql
FROM books;

This query returns the index of the first occurrence of “SQL” in each title. If “SQL” is not present, the result is 0.

Find Index of Substring in MySQL - Example - Using INSTR() to Find a Word in a Title

2 Using LOCATE() with a Starting Position

To search for the word “Techniques” in each book title, starting from position 10:

</>
Copy
SELECT title, LOCATE('Techniques', title, 10) AS position_of_techniques
FROM books;

This query returns the index of “Techniques” in the title column, starting from the 10th character. If not found, the result is 0.

Find Index of Substring in MySQL - Example - Using LOCATE() with a Starting Position

3 Using LOCATE() in a WHERE Clause

To filter and return titles containing the word “Basics”:

</>
Copy
SELECT title
FROM books
WHERE LOCATE('Basics', title) > 0;

This query filters results to show only titles where “Basics” appears, using LOCATE() to identify titles that contain the substring.

Find Index of Substring in MySQL - Example - Using LOCATE() in a WHERE Clause