Oracle update multiple spaces in a column with a single space

Question

I'm trying to update a column that could possibly have a single space or multiple spaces into just one single space using a plain sql statement not pl sql

I could do it through update table set column_name='' where column_name like '% %' However, there could be some data such as abc def in that column. I do not want to disturb the pattern of that data meaning if want to do it only when the column is filled with white space and not touch columns that have any data.

Answer 1

I would recommend using a regular expression to do this, both to do the replacement and to do the matching:

UPDATE mytable
   SET mycolumn = REGEXP_REPLACE(mycolumn, '\s{2,}', ' ')
 WHERE REGEXP_LIKE(mycolumn, '\s{2,}')

This will replace two or more consecutive whitespace characters (spaces, tabs, etc.) with a single space. If you just want to replace spaces and not tabs, carriage returns, or newlines, use the following:

UPDATE mytable
   SET mycolumn = REGEXP_REPLACE(mycolumn, ' {2,}', ' ')
 WHERE REGEXP_LIKE(mycolumn, ' {2,}')

The reason for using {2,} is so that we don't bother replacing spaces where it need not be done.

Answer 2

With regular expression:

update table set column=regexp_replace(column, ' +', ' ')