INSTR() function in MySQL

INSTR() : This function in MySQL is used to return the location of the first occurrence of a substring within a given string. Syntax :

INSTR(string_1, string_2)

Parameters : This function accepts 2 parameters.

• string_1 - The string where searching takes place.
• string_2 - The string/sub-string which will be searched in string_1.

Returns : It returns the position of the first occurrence of a substring within a given string. Note -

• The function will return 0 if string_2 is not found in string_1.
• INSTR() function only performs case-insensitive searches.

Example-1: Finding the position of a sub-string.

SELECT INSTR("Python is a powerful Language", "powerful") 
AS Found;

Output :

Found
13

Example-2: Showing that INSTR() function is case-insensitive.

SELECT  
 INSTR("Python is a powerful Language", "IS") 
AS 'Found1';
 INSTR("Python is a powerful Language", "is") 
AS 'Found2';

Output :

Found1	Found2
8	8

Example-3: If string_2 is not found in string_1.

SELECT INSTR("Python is awesome", "hey") 
AS Found;

Output :

Found
0

Example-4: All possible errors in INSTR() function. If only one parameter is passed.

SELECT INSTR("Python is a powerful Language") 
AS 'Found';

Output :

Incorrect parameter count in the call to native function 'INSTR'

If three or more parameters are passed.

SELECT INSTR("Python is a powerful Language", "is", "a", "lang) 
AS 'Found';

Output :

Incorrect parameter count in the call to native function 'INSTR'