SQL RIGHT()

The SQL RIGHT() function extracts a specified number of characters from the right side of a given string. This function is useful when you need to retrieve trailing characters from a text field, such as file extensions, codes, or the last few digits of an ID.

The RIGHT() function is widely supported in SQL databases, including SQL Server, MySQL, and PostgreSQL.

In this tutorial, we will go through SQL RIGHT() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL RIGHT() Function

The basic syntax of the SQL RIGHT() function is:

</>
Copy
RIGHT(string, number_of_characters);

Each part of this syntax has a specific purpose:

  • string: The string from which you want to extract characters.
  • number_of_characters: The number of characters to extract from the right side of the string.

The RIGHT() function returns the specified number of characters from the end of the string.

Setup for Examples: Creating the Database and Table

We’ll create a single files table with fields file_name, user_id, and email to demonstrate the RIGHT() function examples.

1. First, create a new database called company_data:

</>
Copy
CREATE DATABASE company_data;

2. Select the company_data database to work with:

</>
Copy
USE company_data;

3. Create a table named files with the fields file_id, file_name, user_id, and email:

</>
Copy
CREATE TABLE files (
    file_id INT PRIMARY KEY AUTO_INCREMENT,
    file_name VARCHAR(100),
    user_id VARCHAR(10),
    email VARCHAR(100)
);

4. Insert sample data into the files table:

</>
Copy
INSERT INTO files (file_name, user_id, email)
VALUES 
('report.pdf', '101', 'alice@example.com'),
('invoice.docx', '10234', 'bob@example.com'),
('summary.txt', '987654', 'charlie@example.net');
SQL RIGHT() - Setup data for examples

With this setup complete, we can run the RIGHT() function examples to test and view results in the files table.

Examples: Using RIGHT() in SQL Queries

We’ll go through examples demonstrating the RIGHT() function in SQL, using it to extract specific parts of strings in the files table.

files table data for examples

1. Extracting File Extensions

To extract the last three characters (file extensions) from the file_name column:

</>
Copy
SELECT file_name, RIGHT(file_name, 3) AS file_extension
FROM files;

This query returns each file_name with the last three characters, which is useful for identifying file types by their extensions.

SQL RIGHT() - Example: Extracting File Extensions

2. Extracting the Last Four Digits of User IDs

To retrieve the last four characters of user_id values:

</>
Copy
SELECT user_id, RIGHT(user_id, 4) AS last_four_digits
FROM files;

This query extracts the last four characters from each user_id, useful for displaying only part of the ID.

SQL RIGHT() - Example: Extracting the Last Four Digits of User IDs

3. Extracting the Domain from Email Addresses

To extract the domain part of each email address, assuming it is the last part after the “@” symbol:

</>
Copy
SELECT email, RIGHT(email, CHARINDEX('@', REVERSE(email)) - 1) AS domain
FROM files;

This query reverses the email string, finds the position of “@”, and then extracts the domain using RIGHT().

Reference:

SQL REVERSE()

FAQs for SQL RIGHT()

1. What does the SQL RIGHT() function do?

The RIGHT() function extracts a specified number of characters from the end of a string, returning them as a new string.

2. Can RIGHT() handle NULL values?

If the input is NULL, RIGHT() returns NULL.

3. Is RIGHT() supported in all SQL databases?

Yes, RIGHT() is supported in major SQL databases, including SQL Server, MySQL, and PostgreSQL.

4. How is RIGHT() different from LEFT()?

RIGHT() extracts characters from the end of a string, while LEFT() extracts characters from the beginning.

5. Can RIGHT() be combined with other string functions?

Yes, RIGHT() can be combined with functions like CONCAT(), LEN(), or REVERSE() for more complex string manipulations.