Sometimes we need to remove all the spaces from the column in PostgreSQL. This thing is possible in PostgreSQL using a function named “REPLACE()”. In this blog, we will be discussing the method using which we can remove all the white spaces present in the column. Let’s get started with the article.

How to Remove All the Spaces From a Column in PostgreSQL?

We can remove the white spacing from start, end and both using the regexp_replace() function and the trim() function by using their variants. However, removing all the spaces from a column is not possible using these two functions.

To remove all the spaces from a column in PostgreSQL, we use the simple REPLACE() function. This function finds a string or a substring in a string and replaces it with the specified substring. It basically accepts 3 parameters. The 1st argument is the string or the column’s name from where the query will find the string/substring(to replace) specified as the 2nd parameter. The last parameter is the substring which is the replacement. To understand this function in detail, you can see the article on REPLACE() function. Let’s consider an example for more clarity.

Example: Remove all the Spaces From a String in PostgreSQL

We will now see this function is used to remove all the whitespaces from a string. Consider the following query:

SELECT REPLACE(' This is Command Prompt ',' ','');

In the above syntax:

● We can see that we have provided a string to the function in which the replacement will occur.

● The second parameter is specified as a space. This means that the function will replace all the spaces from the string.

● The third argument is an empty string. This shows that the spaces will be removed.

Let’s see the output for the above query:

The function has replaced all the whitespaces from the start, end, and in the string. This is how we can remove whitespaces from a string. Now let’s consider a table named “simplesearch” having a column named “document”.

Now if we want to remove all the spaces from the column we will write the following query:

SELECT *, REPLACE(document,' ','') AS no_whitespaces FROM simplesearch;

The query will return a row with no whitespaces as they are removed. The output for the above query will be:

The output given above clearly shows that there is no white space in any entry of each column.

Conclusion

We can remove all the whitespaces from a column in PostgreSQL by making use of the REPLACE() function. In the function, we need to specify the string or column name from where we want to replace the substring (specified as the second argument) with the substring (specified as the third argument). In this article, we have learned how the REPLACE() function assists in removing all the white spaces from a column.