The First Cry of Atom

Light way to remove whitespace in PostgreSQL

We might have the experience to remove the white space in the string recorded in PostgreSQL. There is a function TRIM, but it only removes the white space on the left/right side of the string. How can we do that when we want to omit the whitespace in the middle of the given string?

REGEXP_REPLACE is available to replace any string with the regular expression pattern.

SELECT regexp_replace(some_string, '[\s+]', '', 'g') FROM table;

By using the flag g, it replaces all characters appearing in the given string. That would be a flexible and powerful way to replace any characters with PostgreSQL.

Photo by Markus Spiske on Unsplash