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.