Tutorial Gateway

MySQL Instr String

by

How to write instr in MySQL with example?. The MySQL Instr string is one of the String Functions, which returns the position of the first occurrence of a specified substring from a given string.

MySQL Instr String Syntax

The basic syntax of the Instr in MySQL is as shown below.

SELECT INSRT (String, Sub_String) FROM Source
  • String: A valid string or expression in which you want to look.
  • Sub_String: String that you are looking for.

The Instr function in MySQL returns the index position of the first occurrence of a Sub_String in a String. If then Sub_String is not found, it returns 0 as output.

MySQL Instr String Example 1

The following query shows multiple ways to use this string Instr function using different string values.

SELECT INSTR('TutorialGateway', 'a') AS Instr1;

SELECT INSTR('MySQL Tutorial at Tutorial Gateway', 'Tutorial') AS Instr2;

SELECT INSTR('We are abc working at abc company', 'abc') AS Instr3;

SELECT INSTR('We are abc working at abc company', 'MySQL') AS Instr4;

OUTPUT

MySQL Instr String 1

ANALYSIS

Within this string function example, the below statement, we used Instr to find the index position of the first occurrence of a

SELECT INSTR('TutorialGateway', 'a') AS Instr1;

In the last MySQL statement, we are looking for a substring that does not exist.

SELECT INSTR('We are abc working at abc company', 'MySQL') AS Instr4;

Instr String Example 2

The MySQL String Instr function also allows you to write the index position from column values too. To demonstrate this string function, We are going to use the below-shown data.

MySQL Instr String 2

In this example, we are going to use this string Instr function to find the index position of High word in the Education column.

-- MySQL String Instr Function Example
USE `mysql tutorial`;
SELECT  EmpID,
		FirstName,
        LastName,
        Education,
        INSTR(Education, 'High') AS InsrtFunction,
        Occupation,
		YearlyIncome,
        Sales
FROM employee;

OUTPUT

MySQL Instr String 3