Tutorial Gateway

MySQL Instr String

by

The MySQL Instr string is one of the String Function, which will return the position of the first occurrence of a specified sub string from a given string. In this article we will show you, How to write instr in MySQL with example.

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 will return the index position of the first occurrence of a Sub_String in a String. If then Sub_String is not found then it will return 0 as output..

MySQL Instr String Example 1

The following query will show multiple ways to use this Instr function.

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

In below statement, we used Instr to find the index position of first occurrence of a

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

In the last statement, we are looking for a sub string that does not exists.

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

MySQL Instr String Example 2

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

MySQL Instr String 2

In this example, We are going to use this Instr function to find the index position of High word in 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

Thank You for Visiting Our Blog