RIGHT() in SQL

Extracts a specified number of characters from the end (right side) of a string.

Syntax

RIGHT(string, number_of_characters)

Return type

VARCHAR (or equivalent string type in the respective DBMS)

RIGHT() Function Example

SELECT RIGHT('Hello World', 5);
-- Output: 'World'

What is RIGHT() in SQL?

The RIGHT() function in SQL extracts a specified number of characters from the end (right side) of a string. It is useful for retrieving fixed-length identifiers, suffixes, or portions of text where the relevant data appears at the end. This function is primarily supported in SQL Server and some other databases, whereas MySQL and PostgreSQL achieve similar results using SUBSTRING(). The RIGHT() function is commonly applied in scenarios such as extracting file extensions, last digits of account numbers, or domain names from email addresses, making it valuable for text processing and data manipulation tasks.

Parameters:

string: The input string to extract from
number_of_characters: Number of characters to extract from the right

Example Use Cases:

-- Extract file extension

SELECT RIGHT(filename, 3) AS file_extension FROM files;
-- Output:
filename            file_extension
-----------------   --------------
document.pdf        pdf
image.jpg           jpg
script.sql          sql
report.txt          txt
backup.zip          zip

-- Get last four digits of credit card

SELECT RIGHT(card_number, 4) AS last_digits FROM payments;
-- Output:


card_number             last_digits
---------------------   -----------
**** **** **** 5678    5678
**** **** **** 1234    1234
**** **** **** 9012    9012
**** **** **** 3456    3456
**** **** **** 7890    7890

-- Extract year from date string

-- Extract year from date string
SELECT RIGHT(date_string, 4) AS year FROM events;
-- Output:
date_string          year
-----------------   ------
15-03-2024          2024
31-12-2023          2023
01-01-2024          2024
30-06-2023          2023
15-09-2024          2024

Notes:

Behavior: Returns the entire string if the requested length exceeds the string length, an empty string for non-positive lengths, and includes spaces as characters.
Performance Considerations: Efficient for basic string operations
Version Info: Common function, but not available in all DBMS
Deprecated/Recommended Alternatives: SUBSTRING() is a universal alternative