Replace Leading and Trailing characters (Pattern) in Postgresql

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 and Trailing characters (Pattern) in Postgresql 1

 

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

Replace Leading and Trailing characters (Pattern) in Postgresql 2

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 Leading and Trailing characters (Pattern) in Postgresql 2

 

 

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

Replace Leading and Trailing characters (Pattern) in Postgresql 3

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

Replace Leading and Trailing characters (Pattern) in Postgresql 3

 

                                                                                               

Author

  • Sridhar Venkatachalam

    With close to 10 years on Experience in data science and machine learning Have extensively worked on programming languages like R, Python (Pandas), SAS, Pyspark.