Remove Leading, Trailing and all space of column in postgresql

In order to remove leading, trailing and all space of column in postgresql, we use TRIM function, LTRIM, RTRIM and BTRIM function. Strip leading and trailing space in postgresql is accomplished using TRIM function. Let’s see how to

  • Remove Leading space of column in postgresql with LTRIM function
  • Remove Trailing space of column in postgresql with RTRIM function
  • Remove both leading and trailing space of column in postgresql with BTRIM function
  • Remove all the space of column in postgresql

We will be using states table.

Remove Leading, Trailing and all space of column in postgresql 1

 

Remove Leading space of column in postgresql :

To remove leading space of column in postgresql we use TRIM function or LTRIM function

Method 1: using TRIM function

SELECT *,TRIM(LEADING FROM state_name) as state_name_L from states

In Resultant table left space is stripped.

Remove Leading, Trailing and all space of column in postgresql 2a

Method 2: using LTRIM function


SELECT *,LTRIM(colname) as  state_name_L from states

Remove Leading, Trailing and all space of column in postgresql 2b

 

 

Remove TRAILING space of column in postgresql :

To remove trailing space of column in postgresql we use TRIM function or RTRIM function

Method 1: using TRIM function


SELECT *,TRIM(TRAILING FROM state_name) as state_name_R from states

In Resultant table right space is stripped.

Remove Leading, Trailing and all space of column in postgresql 3a

 

Method 2: using RTRIM function


SELECT *,RTRIM(state_name) as state_name_R from states

Remove Leading, Trailing and all space of column in postgresql 3b

 

 

Remove both LEADING and TRAILING space of column in postgresql :

To remove both leading and  trailing space of column in postgresql we use TRIM function or BTRIM function

Method 1: using TRIM function


SELECT *, TRIM(BOTH FROM state_name) as state_name_B from states

In Resultant table both left and right space is stripped.

Remove Leading, Trailing and all space of column in postgresql 4a

Method 2: using BTRIM function


SELECT *,BTRIM(state_name) as state_name_B from states

Remove Leading, Trailing and all space of column in postgresql 4b

 

 

Remove all the spaces of the column in postgresql

To remove all the space of the column we will be using replace function as shown below


SELECT *,REPLACE(state_name,' ','') as state_name_B from states

So the resultant table will be

Remove Leading, Trailing and all space of column in postgresql 5