MySQL POSITION() – Find the position of a substring in a string in MySQL

By /
POSITION Function

In this tutorial, we will study the MySQL POSITION() function. Suppose you want to find where a substring value is located in a string. Operations like these can be common for string operations and manipulations on data in tables. This is where we use the MySQL POSITION() function.

The MySQL POSITION() is used to find out the position of the first occurrence of a substring within a string. If the string does not contain the substring, then the position is returned as zero. It is important to note that the POSITION() function performs a multi-byte safe and case-insensitive search. The POSITION() function is usually used with the SELECT statement. 

Syntax of MySQL POSITION()

POSITION(substring IN string)Code language: SQL (Structured Query Language) (sql)

Where ‘substring’ is the string that is to be searched within another given argument ‘string’.

Examples of MySQL POSITION()

Now it’s time to go over some of the examples of the MySQL POSITION() function here. We’ll start with the most basic examples.

1. Basic Examples

Let us write queries for the following tasks. Below is a list of substrings whose position in a string is to be found out. We will make use of an alias named Result for all our queries so that the output is readable.

Find the position of “H” in “Hello”.

SELECT POSITION(“H” IN “Hello”) AS Result;Code language: SQL (Structured Query Language) (sql)

Output –

Position Basic Example 1

Is MySQL POSITION() Case Sensitive? – Let’s find the position of “DEV” in “JournalDev” to verify the same.

SELECT POSITION(“DEV” IN “JournalDev”) AS Result;Code language: SQL (Structured Query Language) (sql)

POSITION() is case-insensitive and so our output is,

Position Basic Example 2

No Match – Find the position of “Dog” in “I have a cat”.

SELECT POSITION(“Dog” IN “I have a cat”) AS Result;Code language: SQL (Structured Query Language) (sql)

Our output is 0 since “Dog” is not present in the given string.

Position Basic Example 3

First Occurrence Only – Find the position of “India” in “India is a diverse country. I stay in India”. 

SELECT POSITION(“India” in “India is a diverse country. I stay in India”) AS Result;Code language: SQL (Structured Query Language) (sql)

POSITION() returns the position of the first occurrence of the substring in the given string, so our output is,

MySQL Position Basic Example 4

NULL Values – Find the position of NULL in “Humpty Dumpty” and “Hi” in NULL.

SELECT POSITION(NULL IN “Humpty Dumpty”) AS Result; 
SELECT POSITION(“Hi” IN NULL) AS Result;Code language: SQL (Structured Query Language) (sql)

Output –

MySQL Position Basic Example 5

2. With Table Data

Consider the below Employee table.

Employee Table Reverse
Employee Table

Consider the below query – 

SELECT DISTINCT Department, POSITION('T' IN Department) AS Position_Of_T FROM Employee WHERE Department='Executive';Code language: SQL (Structured Query Language) (sql)

We have used the DISTINCT keyword and WHERE clause to find out the position of ‘T’ in the department named ‘Executive’. The output is,

Position Example With Table

Conclusion

The MySQL POSITION() function is a useful function to check if a string is present in a string. It can be very useful in exploratory data analysis and string functions.