MySQL - POSITION() Function

---

---

The MySQL POSITION() function is used to find the position of a substring within a given string.

It takes a string value and a substring as parameters and returns the position of the first occurrence of the specified substring in the original string.

The position of first character in the string will start from 1. If the substring is not found within the original string, it returns 0 as the output. It is case-insensitive search.

The MySQL POSITION() is the synonym for the LOCATE() function.

Syntax

Following is the syntax of MySQL POSITION() function −

POSITION(substring IN string);

Parameters

This function takes a substring and a string to search as parameter.

Return Value

This function returns the position of the first occurrence of the substring within the string or 0 if not found.

Example

In the following example, we are using the POSITION() function to find the position of the substring 'how' within the given string −

SELECT POSITION('how' IN 'Hello how are you');

Following is the output of the above code −

POSITION('how' IN 'Hello how are you')
7

Example

You can also pass numerical values as arguments to the first parameter in the form of a string −

SELECT POSITION('6522 7262 8972 92 112' IN 5);

The output obtained is as follows −

POSITION('6522 7262 8972 92 112' IN 5)
0

Example

If the substring provided is not found within the given string, the function returns 0 −

SELECT POSITION('unknown' IN 'Tutorialspoint');

The result produced is as follows −

POSITION('unknown' IN 'Tutorialspoint')
0

Example

If any of the arguments passed to this function is NULL, it returns NULL −

SELECT POSITION(NULL IN 'Tutorialspoint');

Following is the output of the above code −

POSITION(NULL IN 'Tutorialspoint')
NULL

Example

Let us create a table named "EMP" and insert records into it using CREATE and INSERT statements as shown below −

CREATE TABLE EMP(
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,
   INCOME FLOAT
);

Now, let us insert records into it using the INSERT statement −

INSERT INTO EMP VALUES 
('Krishna', 'Sharma', 19, 2000),
('Raj', 'Kandukuri', 20, 7000),
('Ramya', 'Ramapriya', 25, 5000),
('Mac', 'Mohan', 26, 2000);

The EMP obtained is as follows −

FIRST_NAME	LAST_NAME	AGE	INCOME
Krishna	Sharma	19	2000
Raj	Kandukuri	20	7000
Ramya	Ramapriya	25	5000
Mac	Mohan	26	2000

Following query searches for 'ma' in the 'LAST_NAME' column of the EMP table −

SELECT FIRST_NAME, LAST_NAME, AGE, POSITION('ma' IN LAST_NAME) as result
FROM EMP;

After executing the above code, we get the following output −

FIRST_NAME	LAST_NAME	AGE	result
Krishna	Sharma	19	5
Raj	Kandukuri	20	0
Ramya	Ramapriya	25	3
Mac	Mohan	26	0