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() 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
Syntax of MySQL POSITION()
POSITION(substring IN string)
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;
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;
POSITION() is case-insensitive and so our output is,
No Match – Find the position of “Dog” in “I have a cat”.
SELECT POSITION(“Dog” IN “I have a cat”) AS Result;
Our output is 0 since “Dog” is not present in the given string.
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;
POSITION() returns the position of the first occurrence of the substring in the given string, so our output is,
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;
2. With Table Data
Consider the below Employee table.
Consider the below query –
SELECT DISTINCT Department, POSITION('T' IN Department) AS Position_Of_T FROM Employee WHERE Department='Executive';
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.