In order to replace leading and trailing character (pattern) in postgresql, we use TRIM function, LTRIM, RTRIM function. leading and trailing character in postgresql is replaced using TRIM function. Let’s see how to
- Replace Leading character of column in postgresql with LTRIM function
- Replace Trailing character of column in postgresql with RTRIM function
We will be using states table.
Replace Leading character of column in postgresql.
In order to replace leading character of the column in postgresql we use TRIM and LTRIM function as shown below.
Method 1: using TRIM Function
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
Replace TRAILING character of column in postgresql:
In order to replace TRAILING character of the column in postgresql we use TRIM and RTRIM function as shown below.
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