MySQL Tutorial MySQL Advanced MySQL Database Account Management MySQL References

MySQL INSTR() Function



The MySQL INSTR() function returns the location of the first occurrence of a specified substring in a given string. If the substring is not found within the given string, this function returns 0.

This function performs a case-insensitive search. Note that the first position in string starts with 1.

Syntax

INSTR(string, substring)

Parameters

string Required. Specify the string to search.
substring Required. Specify the substring to search for in string.

Return Value

Returns the location of the first occurrence of the substring in the string.

Example 1:

The example below shows the usage of INSTR() function.

mysql> SELECT INSTR('AlphaCodingSkills.com', 'A');
Result: 1

mysql> SELECT INSTR('AlphaCodingSkills.com', 'Coding');
Result: 6

mysql> SELECT INSTR('AlphaCodingSkills.com', '.com');
Result: 18

mysql> SELECT INSTR('AlphaCodingSkills.com', 'l');
Result: 2

mysql> SELECT INSTR('AlphaCodingSkills.com', 'Z');
Result: 0

mysql> SELECT INSTR('AlphaCodingSkills.com', 'ABC');
Result: 0

Example 2:

Consider a database table called Employee with the following records:

PhoneNumberEmpIDAddress
+1-80XXXXX0001Brooklyn, New York, USA
+33-14XXXXX012Grenelle, Paris, France
+31-20XXXXX193Geuzenveld, Amsterdam, Netherlands
+86-10XXXXX4584Yizhuangzhen, Beijing, China
+65-67XXXXX45Yishun, Singapore
+81-35XXXXX726Koto City, Tokyo, Japan

In the query below, the INSTR() function is used to extract the country code from the PhoneNumber column records.

SELECT *, SUBSTR(PhoneNumber, 1, INSTR(PhoneNumber, '-') - 1) AS CountryCode 
FROM Employee;

This will produce the result as shown below:

PhoneNumberEmpIDAddressCountryCode
+1-80XXXXX0001Brooklyn, New York, USA+1
+33-14XXXXX012Grenelle, Paris, France+33
+31-20XXXXX193Geuzenveld, Amsterdam, Netherlands+31
+86-10XXXXX4584Yizhuangzhen, Beijing, China+86
+65-67XXXXX45Yishun, Singapore+65
+81-35XXXXX726Koto City, Tokyo, Japan+81

❮ MySQL Functions

5