MySQL INSTR() Function

In this tutorial, we will learn how to use the MySQL INSTR() function to determine the position of the first occurrence of a given substring.

MySQL INSTR() Function

Using the instr() function, we can provide a string and a substring. The function will determine if the substring exists in the source string. If the substring exists, the function will return the position of the first occurrence of the substring in the source string.

If the substring does not exist in the source string, the function will return 0.

The following demonstrates the syntax of the instr() function:

INSTR(src_string, sub_string);

The function accepts two main parameters:

The src_string refers to the source string in which you wish to search.
The sub_string defines the substring for which you are searching.

It is good to keep in mind that the instr() function is case-insensitive. Hence, the function will only locate matching patterns with disregard to the character casing,

To perform a case-sensitive search, you can use other tools, such as the binary operator.

Example Function Usage

The following examples demonstrate how we can use the instr() function to search for a specific substring.

SELECT INSTR('MySQL is an awesome database engine', 'database') as pos;

The example above will return the starting position of the string “database” from the source string. In this case, the position of the database string is 21 as shown in the output below:

pos|

---+

21|

Example 2

We can use lower() or upper functions to convert a string to lowercase or uppercase. This can help us overcome the case-sensitivity nature of the function.

Example:

SELECT INSTR(lower('MySQL is an awesome database engine'), lower('DATABASE')) <strong>as</strong> pos;

Result:

pos|

---+

21|

This will return a similar value as the first example, as the source string and substring are converted to lowercase before the search operation.

Example 3

We can also use the instr() function with a table column, as shown in the syntax below.

SELECT INSTR(column_name, 'substring’)

FROM table_name;

Example:

CREATE TABLE blogs (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(255) NOT NULL,

content TEXT NOT NULL,

date_posted DATE NOT NULL,

author VARCHAR(255) NOT NULL

);

Insert some data:

INSERT INTO blogs (title, content, date_posted, author)

VALUES ('My First Blog Post', 'This is the content of my first blog post.', '2022-12-09', 'Jane Doe');

INSERT INTO blogs (title, content, date_posted, author)

VALUES ('My Second Blog Post', 'This is the content of my second blog post.', '2022-12-10', 'Jane Doe');

INSERT INTO blogs (title, content, date_posted, author)

VALUES ('My Third Blog Post', 'This is the content of my third blog post.', '2022-12-11', 'Jane Doe');

select * from blogs;

Resulting table:

We can use the instr() function to get the position of the substring ‘blog’ in the content column as shown:

select title, instr(content, 'post') from blogs;

Result:

Conclusion

In this tutorial, you learned how to use the INSTR() function in MySQL to find the position of a substring within a string. The function is case-sensitive. Hence, you may need to use functions such as lower and upper to convert the search strings to your desired cases.

MySQL INSTR() Function