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
TRIM() function with LEADING keyword will remove the leading space
In Resultant table left space is stripped.
Method 2: using LTRIM function
LTRIM() function will remove the leading space as shown below
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_T from states
TRIM() function with TRAILING keyword will remove the trailing space .
In Resultant table right space is stripped.
Method 2: using RTRIM function
RTRIM() function will remove the trailing space as shown below
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
BTRIM() function will remove both Leading and trailing space as shown below
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
REPLACE() function with column name as argument followed by space and followed by empty string will replace all the space with empty string which is nothing but remove all space of the column
So the resultant table will be
