11 November 2020

In PostgreSQL, the trim() function can be used to remove leading and trailing whitespace from a string. However, if you want to remove all whitespace from a string, you can use a combination of the trim() function and the regexp_replace() function.

To remove all whitespace from a string in PostgreSQL, you can first use the trim() function to remove leading and trailing whitespace from the string. Then, you can use the regexp_replace() function to replace any remaining whitespace characters with an empty string.

The regexp_replace() function takes four arguments: the input string, the regular expression to match, the replacement string, and an optional flag that indicates whether the replacement should be global or not. To remove all whitespace characters from the input string, you can use the regular expression '\s+', which matches one or more whitespace characters (such as spaces or tabs).

When you run the query, the result will be a new string with all whitespace characters removed. This can be useful for cleaning up user input or manipulating string data in other ways.

Source code viewer
  1. UPDATE [schema].[table]
  2. SET [column] = TRIM(REGEXP_REPLACE(REGEXP_REPLACE([column], '\s+$', ''), '^\s+', ''), ' ');
Programming Language: PostgreSQL