+2 votes
in Databases by (71.8k points)
edited by
In a column of my MySQL table, some rows have multiple blanks. How can I replace those consecutive multiple blanks with one blank?

1 Answer

+1 vote
by (353k points)
selected by
Best answer

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.


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