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.