MySQL RIGHT() Function

---

---

The MySQL RIGHT() function accepts a string value and a numerical value (say N) as parameters and returns the specified string up to N characters from right to left.

This function can be useful in various scenarios such as extracting a file extension, area code or any other text string from the right end of the string.

Syntax

Following is the syntax of MySQL RIGHT() function −

RIGHT(str, length)

Parameters

This function takes a string value and a specified number of characters as parameter.

Return Value

This function returns the rightmost portion of the string with the specified length.

Example

In the following example, the function extracts the last 5 characters from the string 'TUTORIALSPOINT' −

SELECT RIGHT('Tutorialspoint', 5);

Following is the output of the above code −

RIGHT('Tutorialspoint', 5)
POINT

Example

If the specified number of characters is larger than the length of the string, the function returns the entire string without any change −

SELECT RIGHT('Hello', 10);

The output obtained is as follows −

RIGHT('Hello', 10)
Hello

Example

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

SELECT RIGHT(NULL, 5);

The output obtained is as follows −

RIGHT(NULL, 5)
0x

Example

You can also pass numerical values as an argument to this function −

SELECT RIGHT(763275825171, 6);

We get the output as follows −

RIGHT(763275825171, 6)
825171

Example

You can also use variables to store strings and then apply the RIGHT() function −

SET @str = '05/20/2023';
SELECT RIGHT(@str, 4) AS 'year';

We get the output as follows −

year
2023

Example

You can also pass column name of a table as an argument to this function and print the desired characters in it.

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 retrieves the last 3 characters in the column "FIRST_NAME" using the RIGHT() function −

SELECT FIRST_NAME, LAST_NAME, AGE, RIGHT(FIRST_NAME, 3) as Result 
FROM EMP;

Output

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

FIRST_NAME	LAST_NAME	AGE	Result
Krishna	Sharma	19	hna
Raj	Kandukuri	20	Raj
Ramya	Ramapriya	25	mya
Mac	Mohan	26	Mac

Example

Assume we have created another table with the name PERSONS using the following query −

CREATE TABLE PERSONS(
   ID INT AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(100) NOT NULL,
   AGE VARCHAR(20) NOT NULL
);

Here, we are inserting records into the above-created table using INSERT INTO statement as follows −

INSERT INTO PERSONS(NAME, AGE) VALUES 
('Navjot singh', 32),
('Virat kohli', 23),
('Ramandeep kaur', 53),
('Deepak chahar', 78),
('Harry brook', 27);

The PERSONS obtained is as follows −

ID	NAME	AGE
1	Navjot singh	32
2	Virat kohli	23
3	Ramandeep kaur	53
4	Deepak chahar	78
5	Harry brook	27

In the following query, we are fetching the lastname of every person from PERSONS table. To do so, following is the approach −

• Firstly, we use the INSTR() function to find the location of the space (" ") in the name.
• Secondly, we use the LENGTH() function to find the length of the name. Here length of the RIGHT function will be length of the person name minus the location of the space (" ").
• In addition to that, we use the RIGHT() function to extract the last name of the person i.e. the characters after the space (" ").

SELECT NAME, RIGHT(NAME, LENGTH(NAME) - INSTR(NAME, ' ')) AS 'Lastname'
FROM PERSONS;

Output

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

NAME	Lastname
Navjot singh	singh
Virat kohli	kohli
Ramandeep kaur	kaur
Deepak chahar	chahar
Harry brook	brook