MySQL INSTR() Function

INSTR(str,substr) is a String function of MySQL. This method return 1st occurrence of substring substr in string str.

Syntax

In the above syntax:

• select statement is used to retrieve data from the table.
• str indicates the string where searching takes place.
• substr indicates the sub-string which will be searched in str.

Example 1

Explanation: On execution of the above statement, the instr function is used to retrieve the position of 'tech' string in 'tpointtech'.

Output: The output of this example is given below.

Example 2

Explanation: On execution of the above statement, the instr function is used to retrieve the position of 'String' string in 'MySql-Function'.

Output: The output of this example is given below.

MySQL INSTR() function with Table column

The INSTR() function is used to find the position of data into MySQL table.

Take an example of the Teacher table to perform the functioning of MySQL INSTR() function.

Example:

Explanation: On execution of the above statement, the INSTR function is used to retrieve the position of particular string in Qualification column.

Output: The output of this example is given below.

MySQL INSTR() function with WHERE Clause

The WHERE clause is used to restrict data. It is used in conjunction with the INSTR() function to replace the restricted data in a table.

Take an example of the Teacher table to perform the functioning of WHERE clause with MySQL INSTR() function.

Example:

Explanation: On execution of the above statement, the INSTR function is used to retrieve the position of particular string in Email_ID column.

Output: The output of this example is given below.

MySQL INSTR() function with Update Statement

In MySQL, the INSTR() function does not change the data in the table. It returns an updated string, which can be used to update table data in an UPDATE statement.

Take an example of the Teacher table, which contains the following information.

Syntax:

Example:

Explanation: On execution of the above statement, the instr function is used to retrieve the position of particular string in Email_ID column.

If you want to check that the content is updated in the table or not, then use the following statement.

Output: After running this query, the output of the table is given below.

Frequently Asked Questions on the MySQL INSTR() function

1. What do you mean by MySQL INSTR() function?

Answer: The INSTR function searches for substrings within a string. This function outputs an integer that represents the position of the first character of this instance within the string.

2. What are the key points while using MySQL INSTR() function?

Answer: Following are the key points while using MySQL INSTR() function.

• Frequent use of `INSTR()` on large text fields can impact performance.
• It is used in combination with `SUBSTRING()` or `REPLACE()` function for string manipulation tasks.