MySQL INSTR() function

INSTR() function

MySQL INSTR() takes a string and a substring of it as arguments, and returns an integer which indicates the position of the first occurrence of the substring within the string.

This function is useful in -

• You can determine the position of a substring within a string using this function.
• As a boolean check, INSTR() determines whether a string contains a substring.
• Data manipulation tasks can be performed using INSTR(), such as extracting substrings or replacing them based on their position.

Syntax:

INSTR (ori_str, sub_str)

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example : MySQL INSTR() function

The following MySQL statement finds the first occurrence of 'st' in 'myteststring' at the position 5, it returns 5.

Code:

SELECT INSTR('myteststring','st');

Output:

mysql> SELECT INSTR('myteststring','st');
+----------------------------+
| INSTR('myteststring','st') |
+----------------------------+
|                          5 | 
+----------------------------+
1 row in set (0.03 sec)

Example : MySQL INSTR() function with WHERE clause

The following MySQL statement returns a list of books (in the first column of the output) if string 'an' is found within the name of the book, and an integer (in the second column of the output) indicating the position of the first occurrence of the string 'an' within the name of the book. A condition placed after the WHERE clause makes sure that it returns only those books, within which, the position of the first occurrence of substring 'an' is more than 0.

Code:

SELECT book_name, INSTR(book_name,'an') FROM book_mast WHERE INSTR(book_name,'an')>0;

Sample table: book_mast

Output:

mysql> SELECT book_name, INSTR(book_name,'an') FROM book_mast WHERE INSTR(book_name,'an')>0;
+-------------------------------------+-----------------------+
| book_name                           | INSTR(book_name,'an') |
+-------------------------------------+-----------------------+
| Understanding of Steel Construction |                     8 | 
| Transfer  of Heat and Mass          |                     3 | 
| Advanced 3d Graphics                |                     4 | 
| Human Anatomy                       |                     4 | 
| The Experimental Analysis of Cat    |                    18 | 
| Anatomy & Physiology                |                     1 | 
| Networks and Telecommunications     |                    10 | 
+-------------------------------------+-----------------------+
7 rows in set (0.00 sec)

Video Presentation:

All String Functions (Slides presentation)

Previous: INSERT
Next: LCASE