How to Find Index of Substring in MySQL

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

INSTR(string, substring)

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

LOCATE() Syntax

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:

CREATE DATABASE library_db;

2. Use the library_db database:

USE library_db;

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

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255)
);

4. Insert sample data into the books table:

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.

---

Examples: Finding Substring Positions in MySQL

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

---

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

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

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.

---

2. Using LOCATE() with a Starting Position

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

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.

---

3. Using LOCATE() in a WHERE Clause

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

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.