PostgreSQL RIGHT Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL RIGHT() function to return the last n characters in a string.

Introduction to the PostgreSQL RIGHT Function #

In PostgreSQL, the RIGHT() function allows you to extract the last n characters from a string.

Here’s the basic syntax of the RIGHT() function:

RIGHT(string, n)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The RIGHT() function accepts two parameters:

• string: The input string from which you want to extract characters.
• n: The number of characters you want to extract from the input string. If n is negative, the RIGHT() function returns all characters but the first |n| characters.

|n| is the absolute value of n. For example if n is -2, the |n| will be 2.

The RIGHT() function returns a string containing the last n characters of the input string. It returns NULL if the string or the n is NULL.

Basic PostgreSQL RIGHT function examples #

The following query uses the RIGHT() function to return the last three characters from the domain name "pgtutorial.com":

SELECT
  RIGHT('pgtutorial.com', 3) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the RIGHT() function with a negative n (-3) to return all characters of the string "pgtutorial.com" except the first 12 characters:

SELECT
  RIGHT('https://www.pgtutorial.com', -12) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

     result
----------------
 pgtutorial.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following query uses the RIGHT() function with the NULL input string:

SELECT
  RIGHT(NULL, -1) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using the PostgreSQL RIGHT function with table data #

First, create a new table called vendors to store the vendor information:

CREATE TABLE vendors (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL,
  homepage VARCHAR NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, insert some rows into the vendors table:

INSERT INTO
  vendors (id, name, homepage)
VALUES
  (1, 'TechNova', 'https://www.technova.com'),
  (2, 'GigaWave', 'https://www.gigawave.net'),
  (3, 'VisionaryE', 'https://www.visionarye.org'),
  (4, 'HyperGadget', 'https://www.hypergadget.com'),
  (5, 'NeoDevices', 'https://www.neodevices.net'),
  (6, 'QuantumTech', 'https://www.quantumtech.org'),
  (7, 'AstroDigital', 'https://www.astrodigital.com'),
  (8, 'InnoGears', 'https://www.innogears.net'),
  (9, 'Futura', 'https://www.futuraelectro.org'),
  (10, 'NextGens', 'https://www.nextgensystems.com')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id |     name     |            homepage
----+--------------+--------------------------------
  1 | TechNova     | https://www.technova.com
  2 | GigaWave     | https://www.gigawave.net
  3 | VisionaryE   | https://www.visionarye.org
  4 | HyperGadget  | https://www.hypergadget.com
  5 | NeoDevices   | https://www.neodevices.net
  6 | QuantumTech  | https://www.quantumtech.org
  7 | AstroDigital | https://www.astrodigital.com
  8 | InnoGears    | https://www.innogears.net
  9 | Futura       | https://www.futuraelectro.org
 10 | NextGens     | https://www.nextgensystems.comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, use the RIGHT function to get the domain extensions:

SELECT
  homepage,
  RIGHT(homepage, 3) extension
FROM
  vendors
ORDER BY
  homepage;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            homepage            | extension
--------------------------------+-----------
 https://www.astrodigital.com   | com
 https://www.futuraelectro.org  | org
 https://www.gigawave.net       | net
 https://www.hypergadget.com    | com
 https://www.innogears.net      | net
 https://www.neodevices.net     | net
 https://www.nextgensystems.com | com
 https://www.quantumtech.org    | org
 https://www.technova.com       | com
 https://www.visionarye.org     | orgCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Filtering data using the RIGHT function #

The following query uses the RIGHT() function in the WHERE clause to get the domains whose extensions are ".com" :

SELECT
  homepage,
  RIGHT(homepage, 3) extension
FROM
  vendors
WHERE
  RIGHT(homepage, 3) = 'com'
ORDER BY
  homepage;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

            homepage            | extension
--------------------------------+-----------
 https://www.astrodigital.com   | com
 https://www.hypergadget.com    | com
 https://www.nextgensystems.com | com
 https://www.technova.com       | comCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Aggregating data using the RIGHT function #

The following query uses the RIGHT() function with the COUNT aggregate function to group domain names by their extensions:

SELECT
  RIGHT(homepage, 3) extension,
  COUNT(*)
FROM
  vendors
GROUP BY
  extension
ORDER BY
  homepage;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 extension | count
-----------+-------
 com       |     4
 org       |     3
 net       |     3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the RIGHT() function to extract the last n characters from a string.