In PostgreSQL the right()
function returns the specified number of rightmost characters in a given string.
We have the option of specifying the number of characters to return from the right or the number of characters to omit from the left. We do this by specifying a positive integer (to return n number of rightmost characters) or a negative integer (to return everything except n leftmost characters).
Example
Here’s a quick example to demonstrate:
SELECT right('PostgreSQL', 3);
Result:
SQL
In this example I used a positive integer (3
) to return the three rightmost characters.
Using a Negative Count
As mentioned, we can use a negative integer to return everything except the first n
characters from the string:
SELECT right('PostgreSQL', -3);
Result:
tgreSQL
So in this case we returned everything except the first three characters.
Trimming White Space
If the original string contains white space on the right or left this can affect the result. We can use the trim()
function to remove white space from the string:
SELECT
right(' PostgreSQL ', 3) AS "Untrimmed",
right(trim(' PostgreSQL '), 3) AS "Trimmed";
Result:
Untrimmed | Trimmed
-----------+---------
QL | SQL
And here it is with a negative value:
SELECT
right(' PostgreSQL ', -3) AS "Untrimmed",
right(trim(' PostgreSQL '), -3) AS "Trimmed";
Result:
Untrimmed | Trimmed
-----------+---------
stgreSQL | tgreSQL
We can also use btrim()
, rtrim()
and ltrim()
if required.
Passing Null Values
If any argument is null
, then null
is returned:
\pset null 'null'
SELECT
right('PostgreSQL', null),
right(null, 3),
right(null, null);
Result:
right | right | right
-------+-------+-------
null | null | null