MySQL RIGHT() Function

Summary: in this tutorial, you will learn how to use the MySQL RIGHT() function to get a specified number of rightmost characters from a string.

MySQL RIGHT() function overview

The MySQL RIGHT() function extracts a specified number of rightmost characters from a string.

Here is the syntax of the RIGHT() function:

RIGHT(str,length)Code language: SQL (Structured Query Language) (sql)

The RIGHT() function accepts two arguments:

• str is the string from which you want to extract the substring.
• length is the number of the rightmost characters that you want to extract from the str.

The RIGHT() function will return NULL if any argument is NULL.

MySQL RIGHT() function examples

Let’s take some examples of using the RIGHT() function.

1) Simple RIGHT() function example

This example uses the RIGHT() function to extract 3 rightmost characters from the string MySQL:

SELECT RIGHT('MySQL', 3);Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------+
| RIGHT('MySQL', 3) |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

2) Using the RIGHT() function to extract date fields from a date string

The following example uses the RIGHT(), LEFT(), and SUBSTRING() functions to extract date fields from a date string:

SET @str = '12/31/2019';
SELECT 
    RIGHT(@str, 4) year,
    LEFT(@str, 2) month,
    SUBSTRING(@str, 4, 2) day;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

+------+-------+------+
| year | month | day  |
+------+-------+------+
| 2019 | 12    | 31   |
+------+-------+------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)

3) Using the RIGHT() function with table data

We’ll use the products table from the sample database:

This example uses the RIGHT() function to extract the number part after the "_" character of the product code:

SELECT 
    productCode,
    RIGHT(productCode,
        LENGTH(productCode) - INSTR(productCode, '_')) productNo
FROM
    products;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, use the INSTR() function to find the location of the first occurrence of the underscore (_) in the product code. Notice that the product code contains only one underscore (_) character.
• Second, use the LENGTH() function to return the length of the product code. The length of the number part equals the length of productCode minus the location of the ‘_’ character.
• Third, use the RIGHT() function to extract the number part.

The following picture shows the partial output:

+-------------+-----------+
| productCode | productNo |
+-------------+-----------+
| S10_1949    | 1949      |
| S10_4757    | 4757      |
| S10_4962    | 4962      |
| S12_1099    | 1099      |
...

Summary

• Use the MySQL RIGHT() function to get a specified number of rightmost characters from a string.