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'

My Personal Notes arrow_drop_up

Save