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 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.
Method 2: using LTRIM function
SELECT *,LTRIM(colname) as state_name_L from states
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.
Method 2: using RTRIM function
SELECT *,RTRIM(state_name) as state_name_R from states
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.
Method 2: using BTRIM function
SELECT *,BTRIM(state_name) as state_name_B from states
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
