How to find the position of a substring in a string using the INSTR function in MySQL

How to use the INSTR function in MySQL to find the position of a substring in a string

MySQL is a commonly used relational database management system that provides a rich function library to process and operate data. Among them, the INSTR function is a very practical function, used to find the position of a substring in the original string. In this article, I will show you how to use the INSTR function in MySQL and provide code examples.

The basic syntax of the INSTR function is as follows:

INSTR(str,sub_str)

Among them, str is the original string and sub_str is the substring to be found. This function returns the position of sub_str in str, or 0 if not found.

Below, let us demonstrate how to use the INSTR function through some examples.

Example 1: Find the position of the substring in the original string

Suppose we have a table named "students", in which there is a column named "first_name", which stores students name. We want to find all students whose names contain the substring "an" and return their position in the name.

SELECT first_name, INSTR(first_name, 'an') AS position
FROM students
WHERE INSTR(first_name, 'an') > 0;

After running the above code, the names of students containing the substring "an" and the position of the substring in the name will be returned.

Example 2: Find the position range of the substring in the original string

Sometimes, we may need to limit the search range of the substring in the original string. MySQL's INSTR function provides a third parameter to specify the location from which to start the search.

Suppose we have a table named "products" with a column named "description" that stores the description of the product. We want to find all products whose descriptions begin with "Made in" and return the position of "Made in" in the description, while limiting the search to the first 20 characters of the description.

SELECT description, INSTR(description, 'Made in', 1, 20) AS position
FROM products
WHERE INSTR(description, 'Made in', 1, 20) > 0;

After running the above code, it will return the description of the product that starts with "Made in" and the position of "Made in" in the description.

Example 3: Determine whether a substring exists

In addition to returning the position of the substring in the original string, the INSTR function can also be used to determine whether the substring exists.

Suppose we have a table named "employees" with a column named "email" that stores the email addresses of employees. We want to find all employees who have an email address ending in ".com".

SELECT email
FROM employees
WHERE INSTR(email, '.com') > 0;

After running the above code, all employees with email addresses with ".com" as the suffix will be returned.

To sum up, the INSTR function is a very practical MySQL function, used to find the position of a substring in the original string. By using the INSTR function reasonably and flexibly, we can easily complete various complex data processing and query tasks. I hope this article can help you when using MySQL.

The above is the detailed content of How to find the position of a substring in a string using the INSTR function in MySQL. For more information, please follow other related articles on the PHP Chinese website!