Extract First ‘N’ character of the column in PostgreSQL

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

• Extract first ‘N’ character of the column in PostgreSQL using LEFT() function
• Extract first ‘N’ character of the column using SUBSTRING () function

Using LEFT()function
The LEFT() function returns the first N characters from a string.

Syntax of LEFT() Function:

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

Example using LEFT() function:

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

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

 

SELECT LEFT('PostgreSQL', 4) AS first_n_chars;

Output:

Example 2: Extracting the First N Characters from a Column

We will be using Zipcodes table.

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

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

 

select *,LEFT(city,5) as first_n FROM zipcodes

Left function of city column extracts first 5 characters so the resultant table will be

Output:

Using SUBSTRING() Function:

The SUBSTRING() function can also be used to achieve the same result.

Syntax :

• string: The original string or column.
• start: The starting position (1-based index).
• length: The number of characters to extract.

Example Using SUBSTRING() Function

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

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

 

SELECT SUBSTRING('PostgreSQL' FROM 1 FOR 4) AS first_n_chars;

Output:

Example 2: Extracting the First N Characters from a Column

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

 

select *, SUBSTRING(city FROM 1 FOR 5) AS first_n FROM zipcodes

SUBSTRING() function of city column extracts first 5 characters so the resultant table will be

Output: