MySQL INSTR() Function Explained

MySQL offers many string functions to simplify working with textual data. The INSTR() function helps locate the first occurrence of a substring in a string.

The function simplifies string search and helps locate substrings in larger text fields.

This guide explains what the INSTR() function is and shows example usage.

Prerequisites

• MySQL installed.
• Access to the MySQL shell or a tool to execute SQL queries.

What Is MySQL INSTR() Function?

The MySQL INSTR() function is a string operator that helps locate a substring in a given string and returns its position.

The position is 1-based, meaning the first character in the string is at position 1. Therefore:

• If the substring is found, the function returns a number (integer) that shows the position of the first character in the main string.
• If the substring is not found, it returns zero (0).

The function helps validate inputs, extracts data from text fields, and filters query results.

MySQL INSTR() Syntax

The syntax for the INSTR() function is:

INSTR(string, substring)

MySQL INSTR() Parameters

The parameters for the INSTR() function are:

• string. The main text (string) to search.
• substring. The text to search for in the main string.

How to Use MySQL INSTR()?

To effectively use the INSTR() function, see the following tips:

• Case sensitivity. The INSTR() function respects collation. Depending on the selected collation, the function is case-sensitive or insensitive. For example, utfmb4_general_ci is case-insensitive, while the binary utf8mb4_bin collation is case sensitive.
• Empty substrings. If the substring is an empty string (''), the INSTR() function returns 1, which is not intuitive. Validate the input to address empty substring behavior.
• LOCATE(). The LOCATE() function is similar to INSTR(), but the parameter order is reversed. Choose a function that aligns with your coding style.
• Optimization. Avoid using large text fields, full-table scans, or querying many rows to maintain efficient query performance. Use full-text indexing for frequent searches.
• Combine with other functions and operators. Use INSTR() with other string functions, operators, and conditional logic for advanced manipulation.

The sections below provide hands-on examples.

MySQL INSTR() Examples

The following examples illustrate how the INSTR() function operates in various scenarios. Use a MySQL shell or a tool to execute SQL queries (such as MySQL workbench) to test the examples.

Basic Substring Match

To see how INSTR() works, try this simple substring match:

SELECT INSTR('phoenixNAP', 'NAP');

The output shows that the substring 'NAP' starts at the 8th character in the string 'phoenixNAP'.

Substring Not Found

If a substring is not found in the main string, the function returns zero (0):

SELECT INSTR('phoenixNAP', 'Arizona');

Use this functionality to exclude specific rows from results using conditional logic.

Case-Insensitive Match (Default Collation)

The default collation for MySQL 8+ is utfmb4_general_ci, which is case-insensitive. The INSTR() function finds substrings regardless of case:

SELECT INSTR('phoenixNAP', 'nap');

The function matches the substring, ignoring the case differences.

Case-Sensitive Match (Explicit Collation)

To explicitly state a collation and enable case-sensitive matching, see the following example:

SELECT INSTR('phoenixNAP' COLLATE utf8mb4_bin, 'nap');

The binary collation enforces case sensitivity, and the exact substring is not found.

Multiple Substring Appearance

If a substring appears multiple times in the main string, the INSTR() function provides the location of the first instance only:

SELECT INSTR('phoenixNAP phoenixNAP', 'phoenix');

The code returns the position of the first match, ignoring any subsequent substrings.

Empty Substring

An empty substring returns the first position (1), which is the start of the string:

SELECT INSTR('phoenixNAP', '');

This behavior is counterintuitive. To address this behavior, validate the input when using dynamic search values.

Use With WHERE Clause

To filter results, use INSTR() with a WHERE clause. For example:

SELECT email
FROM users
WHERE INSTR(email, '@gmail.com') > 0;

The query finds all users whose email addresses contain '@gmail'. It shows only the rows where the substring is found in a database table.

Conclusion

This guide explained how the INSTR() function works in MySQL. We also provided several hands-on examples and tips to use the function efficiently.

For more SQL resources, grab our free MySQL commands cheat sheet.