# How to Extract First N or Last N Characters From a Column in PostgreSQL

> We can extract the first and last n characters from a table column and use them as per our needs. This can be done by utilizing the LEFT() and RIGHT() function…

PostgreSQL stores data in the database in the form of tables. We can manipulate these data using the functions offered by PostgreSQL itself. Similarly, PostgreSQL also provides us with functions using which we can extract some information. Sometimes we want to get some characters from the start and end of the column data. This post will assist us in extracting the first n or last n characters from a column in PostgreSQL. Let’s see how it is done.

 **How to Extract First N or Last N Characters From a Column in PostgreSQL**

We can extract any specified number of characters from the start or end of any entry in any column. the **LEFT()** and **RIGHT()** functions can be utilized for this purpose respectively. Let’s see how both these functions work.

 **How to Extract First N Characters From a Column in PostgreSQL**

In order to get the n characters from the start, of a column, we will make use of the **LEFT()** function. The basic syntax of this function is:
    
    
    LEFT(Main_String, no_of_characters)

In the above syntax:

● The **LEFT()** function takes in 2 parameters.

● The 1st argument is the **string** from which we wish to retrieve the characters.

● The 2nd parameter is an **integer,** that depicts the **number of characters** to be fetched from the left.

The LEFT() function will give the particular number of characters from the left i.e. the start of the main string.

 **Note:** The space between words is also considered as a character.

 **Example 1: Extract the First N Characters From a Column Using the Left() Function**

Consider the following query to get the first six characters from the specified string from the start:
    
    
    SELECT LEFT('This is command prompt. ',6);

The query will return the first 6 characters from the string. The above query returns the following output:

We can also specify the **negative integer** in the function. The negative integer will depict that “return all the character except the last n”. For example, if we specify “-9” as the second parameter the query will return all the characters starting from left except the last 9 characters like this:

We can also get the specified number of characters from the table columns. For this let's consider the table named “simplesearch” having a column named “document”.

We can get the first 5 characters from each document using the following query:
    
    
    SELECT LEFT(document,5) FROM simplesearch;

The query results in the following output:

The above output advocated the accurate working of the LEFT() function in the tables as well. Next, we learn to get, in return, n characters from the last in the PostgreSQL column.

 **How to Extract Last N Characters From a Column in PostgreSQL**

In order to get the n characters from the start, of a column, we will make use of the **RIGHT()** function. The syntax of RIGHT() function is:
    
    
    RIGHT(Main_String, no_of_characters)

In the above syntax:

● The **RIGHT()** function takes in 2 parameters.

● The 1st argument is the **string** from where we wish to retrieve the characters.

● The 2nd parameter is an **integer,** which depicts the **number of characters** to be fetched from the right.

The RIGHT() function will give us the particular number of characters from the right i.e. end of the main string.

 **Note:** The space between words is also considered as a character.

 **Example 1: Extract the Last N Characters From a Column Using the Right() Function**

Consider the following query to get the last characters from the specified string from the end
    
    
    SELECT RIGHT('This is command prompt. ',6);

The query will return the last 6 characters from the string. The above query returns the following output:

We can also specify the negative integer in the function. The negative integer will depict that “return all the character except the first n”. For example, if we specify “-9” as the second parameter the query will return all the characters starting from right except the first 9 like this:

We can also get the specified number of characters from the table columns. Consider the same table named “simplesearch” that we considered above. We can get the last 5 characters from each document using the following query:
    
    
    SELECT RIGHT(document,5) FROM simplesearch;

The query results in the following output:

The above output advocated the accurate working of the RIGHT() function in the tables as well.

We have seen how to get the first n and last n characters from the column in PostgreSQL. So that was all from the topic.

 **Conclusion**

We can extract the first and last n characters from a table column and use them as per our needs. This can be done by utilizing the **LEFT()** and **RIGHT()** functions provided by PostgreSQL. Both of the functions take two parameters; the first one is the string or the column name from where we want to retrieve the characters and the second one is the number of characters we want to retrieve either from the start or the end. In this post, we have discussed the working of these two functions in detail with the help of practical examples.

---
[View this page online](https://www.commandprompt.com/education/how-to-extract-first-n-or-last-n-characters-from-a-column-in-postgresql/)