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
mysql-right-function.htm
Advertisements