REGEXP_REPLACE

Get rid of extra spaces.

WITH t AS (SELECT 'NO MORE  EXTRA      SPACES' str FROM dual)
SELECT REGEXP_REPLACE(str, ' {2,}', ' ') str
  FROM t;

STR                 
--------------------
NO MORE EXTRA SPACES


1 row selected.

This is actually a very neat example of solving a simple problem using regular expressions. Of course regular expressions are so much more powerful, indeed books have been written about them, but that is beyond the scope of this simple example.