Remove Leading and Trailing characters in PostgreSQL

In order to Remove leading and trailing characters (pattern) in PostgreSQL, we use TRIM function, LTRIM, RTRIM function. leading and trailing character in PostgreSQL is Removed using TRIM function. Let’s see how to

• Remove Leading character of column in PostgreSQL with LTRIM() function
• Remove Leading character of column with TRIM() function
• Remove Trailing character of column in PostgreSQL with RTRIM() function
• Remove Trailing character of column with TRIM() function

We will be using states table.

Remove Leading character of column in PostgreSQL.

In order to Remove leading character of the column in PostgreSQL we use TRIM() and LTRIM() function as shown below.

Method 1: using TRIM() Function

Syntax of TRIM Function:

• LEADING: Removes characters from the beginning of the string.
• TRAILING: Removes characters from the end of the string.
• BOTH: Removes characters from both the beginning and the end of the string (this is the default if no direction is specified).
• characters: The characters to be removed. If omitted, it defaults to removing spaces.
• string: The string from which to remove the characters.

Example TRIM() function

To Remove Leading Character “A” in the PostgreSQL Table using TRIM() function we have used the below code

 

SELECT *,TRIM(LEADING,'A') as state_name_B from states

So the resultant table will be

Method 2: using LTRIM Function

 

SELECT *,LTRIM(state_name,'A') as state_name_B from states

So the resultant table will be leading character “A” removed

Remove TRAILING character of column in PostgreSQL.

In order to Remove TRAILING character of the column in PostgreSQL we use TRIM and RTRIM function as shown below.

Method 1: using TRIM Function

 

SELECT *,TRIM(TRAILING,'L') as state_name from states

So the resultant table will be

Method 2: using RTRIM Function

 

SELECT *,RTRIM(state_name,'L') as state_name from states

So the resultant table will be Trailing character “L” removed