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 −

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 −

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 −

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 −

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 −

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 −