Extract Last ‘N’ character of the column in PostgreSQL

In PostgreSQL to  Extract the Last ‘N’ character of a column  we will be using the SUBSTRING() function (also known as SUBSTR()) or the RIGHT() function. Both functions can achieve the desired result, but RIGHT() is more straightforward for this specific task.

• Return Last ‘N’ character of the column in PostgreSQL using RIGHT() function
• Return Last ‘N’ character of the column using SUBSTRING () function

Using RIGHT()function
The RIGHT() function extract the Last N characters from a string.

Syntax:

• string: The original string or column from which to extract the characters.
• n: The number of characters to extract from the RIGHT.

Example using RIGHT() function:

Example 1: Extracting the Last N Characters from a Specific String

To extract the Last 4 characters from the string ‘PostgreSQL’:

SELECT RIGHT('PostgreSQL', 4) AS last_n_chars;

Output:

Example 2: Extracting the Last N Characters from a Column

We will be using Zipcodes table.

Return Last ‘N’ character of the column in PostgreSQL:

In order to return Last n character of the column in PostgreSQL. We use RIGHT() function as shown below.

select *,RIGHT(city,5) as last_n FROM zipcodes

RIGHT() function of city column extracts Last 5 characters so the resultant table will be

Output:

Using SUBSTRING() Function:

The SUBSTRING() function can also be used to achieve the same result, by using a negative starting position.

Syntax:

• string: The original string or column.
• start: The starting position (negative for counting from the end).
• length: The number of characters to extract.

Example Using SUBSTRING() Function

Example 1: Extracting the Last N Characters from a Specific String

To extract the Last 4 characters from the string ‘PostgreSQL’:

SELECT SUBSTRING('PostgreSQL' FROM LENGTH('PostgreSQL') - 3 FOR 4) AS last_n_chars;

Output:

Example 2: Extracting the Last N Characters from a Column

To select the Last 5 characters from the CITY column using SUBSTRING():

select *, SUBSTRING(city FROM LENGTH(city) - 4  FOR 5) AS last_n FROM zipcodes

SUBSTRING() function along with negative starting position (negative for counting from the end). extracts Last 5 characters of city column so the resultant table will be

Output: