title: PostgreSQL regexp_replace() to keep just one whitespace
body: <p>I need to clean up a string column with both whitespaces and tabs included within, at the beginning or at the end of strings (it's a mess !). I want to keep just one whitespace between each word. Say we have the following string that includes every possible situation :</p>
  
  <pre><code>mystring = '  one two    three      four    '
  </code></pre>
  
  <ul>
  <li>2 whitespaces before 'one'  </li>
  <li>1 whitespace between 'one' and 'two'  </li>
  <li>4 whitespaces between 'two' and 'three'  </li>
  <li>2 tabs after 'three'  </li>
  <li>1 tab after 'four'  </li>
  </ul>
  
  <p>Here is the way I do it :</p>
  
  <ol>
  <li>I delete leading and trailing whitespaces</li>
  <li>I delete leading and trailing tabs</li>
  <li>I replace both 'whitespaces repeated at least two' and tabs by a sole whitespace</li>
  </ol>
  
  <p></p>
  
  <pre><code>WITH
  
    t1 AS (SELECT'  one two    three      four    '::TEXT AS mystring),
  
    t2 AS (SELECT TRIM(both ' ' from mystring) AS mystring FROM t1),
  
    t3 AS (SELECT TRIM(both '\t' from mystring) AS mystring FROM t2)
  
    SELECT regexp_replace(mystring, '(( ){2,}|\t+)', ' ', 'g') FROM t3 ;
  </code></pre>
  
  <p></p>
  
  <p>I eventually get the following string, which looks nice but I still have a trailing whitespace...</p>
  
  <pre><code>'one two three four '
  </code></pre>
  
  <p>Any idea on doing it in a more simple way and solving this last issue ?</p>
  
  <p>Many thanks !</p>