MySQL - INSTR() Function

---

---

The MySQL INSTR() function is used to find the starting occurrence of a substring within a given string.

It accepts two parameters: the first parameter is the original string and the second parameter is the substring that is to be searched in the original string.

This function finds and returns starting position (index) of a substring within the provided string. If the substring is not found in the original string, this function returns 0. If you pass NULL to either of the parameters, this function returns NULL as result.

Syntax

Following is the syntax of MySQL INSTR() function −

INSTR(str,substr)

Parameters

This function takes a string and a substring to search for as a parameter.

Return Value

This function returns the position of the first occurrence of the substring within the string or 0 if not found.

Example

In the following example, we are searching for the substring 'how' within the given string −

SELECT INSTR('Hello how are you', 'how');

Following is the output of the above code −

INSTR('Hello how are you', 'how')
7

Example

If the substring provided is not found in the given string, the function returns 0 −

SELECT INSTR('Tutorialspoint', 'unknown');

The output obtained is as follows −

INSTR('Tutorialspoint', 'unknown')
0

Example

You can also pass numerical values as string arguments to this function −

SELECT INSTR('638235 25 28 261 91', '25');

We get the output as follows −

INSTR('638235 25 28 261 91', '25')
8

Example

If any of the arguments passed to this function is NULL, it returns NULL −

SELECT INSTR(NULL, 'point');

The result produced is as shown below −

INSTR(NULL, 'point')
NULL

Example

You can also search for a substring in the values of a column of a table using the INSTR() function.

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME	LAST_NAME	AGE	INCOME
Krishna	Sharma	19	2000
Raj	Kandukuri	20	7000
Ramya	Ramapriya	25	5000
Mac	Mohan	26	2000

Following query searches for 'ma' in the 'LAST_NAME' column −

SELECT FIRST_NAME, LAST_NAME, AGE, INSTR(LAST_NAME, 'ma') as RESULT
FROM EMP;

After executing the above code, we get the following output −

FIRST_NAME	LAST_NAME	AGE	RESULT
Krishna	Sharma	19	5
Raj	Kandukuri	20	0
Ramya	Ramapriya	25	3
Mac	Mohan	26	0