Remove Excess Middle Spaces in Name - SQL

Question

I've got a data-set of people's names but the problem is I imagine when some people were typing in their names they hit the spacebar a few times too many b/c now we have this:

Notice how in the name column there're some names like John_Doe, John__Doe, John____Doe, etc. What would be the best way to ensure that whenever there's a _ between words, be it 1,2,3, etc. it's removed/trimmed to only 1 space so all of these records would become John_Doe.

Thoughts?

Solution

This should do the trick

DECLARE @string varchar(100)
SET @string = 'John   Doe'

SELECT string = REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')

Replace duplicate spaces with a single space in T-SQL