SQL RIGHT Function

Summary: in this tutorial, you’ll learn how to use the SQL RIGHT function to return a specified number of characters from the end of a string.

Introduction to the SQL RIGHT Function #

In SQL, the RIGHT function takes a string and returns a specified number of characters from the end ( the right) of a string.

Here’s the syntax of the RIGHT function:

RIGHT(string, number_of_characters)Code language: SQL (Structured Query Language) (sql)

The RIGHT function accepts two parameters:

string: The input string from which you want to return the characters.
number_of_characters: The number of characters you want to return from the input string.

The RIGHT function returns a string that contains a specified number of characters from the right of the input string.

It returns NULL if the input string or the number_of_characters is NULL.

Basic SQL RIGHT function example #

The following query uses the RIGHT function to return the extension of a file name:

SELECT RIGHT('resume.pdf', 3) extension;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 extension ----------- pdfCode language: SQL (Structured Query Language) (sql)

Extracting file extensions #

First, create a new table called performance_evaluations to store the employees’ performance evaluations:

CREATE TABLE performance_evaluations ( employee_id INT PRIMARY KEY, rating INT NOT NULL, evaluation_form VARCHAR(255) NOT NULL );Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert rows into the performance_evaluations table:

INSERT INTO performance_evaluations (employee_id, rating, evaluation_form) VALUES (101, 4, 'neena.pdf'), (102, 3, 'lex.pdf'), (103, 5, 'alexander.doc'), (104, 3, 'bruce.xls'), (105, 3, 'david.xls');Code language: SQL (Structured Query Language) (sql)

Try it

Third, retrieve data from the performance_evaluations table:

SELECT * FROM performance_evaluations;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 employee_id | rating | evaluation_form -------------+--------+----------------- 101 | 4 | neena.pdf 102 | 3 | lex.pdf 103 | 5 | alexander.doc 104 | 3 | bruce.xls 105 | 3 | david.xlsCode language: SQL (Structured Query Language) (sql)

Using the RIGHT function with table data #

The following query uses the RIGHT function to retrieve the employee name, ranting, evaluation forms, and the form’s extensions:

SELECT first_name, rating, evaluation_form, RIGHT(evaluation_form, 3) form_extension FROM performance_evaluations p INNER JOIN employees e ON e.employee_id = p.employee_id ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | rating | evaluation_form | form_extension ------------+--------+-----------------+---------------- Alexander | 5 | alexander.doc | doc Bruce | 3 | bruce.xls | xls David | 3 | david.xls | xls Lex | 3 | lex.pdf | pdf Neena | 4 | neena.pdf | pdfCode language: SQL (Structured Query Language) (sql)

Using the RIGHT function in the WHERE clause #

The following statement uses the RIGHT function in the WHERE clause to find evaluation forms with the extension is pdf or xls:

SELECT first_name, rating, evaluation_form, RIGHT(evaluation_form, 3) form_extension FROM performance_evaluations p INNER JOIN employees e ON e.employee_id = p.employee_id WHERE RIGHT(evaluation_form, 3) IN ('pdf', 'xls') ORDER BY first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name | rating | evaluation_form | form_extension ------------+--------+-----------------+---------------- Bruce | 3 | bruce.xls | xls David | 3 | david.xls | xls Lex | 3 | lex.pdf | pdf Neena | 4 | neena.pdf | pdfCode language: SQL (Structured Query Language) (sql)

Using the RIGHT function with an aggregate function #

The following query uses the RIGHT function with the COUNT aggregate function to get the count for each form extension:

SELECT RIGHT(evaluation_form, 3) form_extension, COUNT(*) extension_count FROM performance_evaluations GROUP BY RIGHT(evaluation_form, 3) ORDER BY form_extension;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 form_extension | extension_count ----------------+----------------- doc | 1 pdf | 2 xls | 2Code language: SQL (Structured Query Language) (sql)

Summary #

Use the RIGHT function to extract a specified number of characters from the end of a string.

Databases #

Was this tutorial helpful ?