SQL : how to replace consecutive multiple spaces in a string with a single space

Question

In a column of my MySQL table, some rows have multiple blanks. How can I replace those consecutive multiple blanks with one blank?

Answer 1

There are several approaches to replace consecutive spaces with one space. You can use regexp_replace() function that is used for pattern matching. It replaces the current string by the new string.

E.g.

mysql> Select regexp_replace('Kushagra Udit', 'Kushagra', 'Kush');
+-----------------------------------------------------+
| regexp_replace('Kushagra Udit', 'Kushagra', 'Kush') |
+-----------------------------------------------------+
| Kush Udit                                           |
+-----------------------------------------------------+

To replace multiple spaces with one space in all rows of a column, try the following SQL code:

UPDATE tableName SET columnName = REGEXP_REPLACE(columnName, ' \+', ' ')

If you want to make changes in a particular row, you need to add WHERE clause in the above SQL code.

UPDATE tableName SET columnName = REGEXP_REPLACE(columnName, ' \+', ' ') WHERE columnName = rowValue