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:
stris the string from which you want to extract the substring.lengthis the number the rightmost characters that you want to extract from thestr.
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.
MySQL RIGHT() function simple 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)
Here is the output:
+-------------------+
| RIGHT('MySQL', 3) |
+-------------------+
| SQL |
+-------------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Using MySQL 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)
Using MySQL RIGHT() function with table data
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 partial output:
In this tutorial, you have learned how to use the MySQL RIGHT() function to get a specified number of rightmost characters from a string.