TSQL: Remove multiple spaces

Just a small tip on removing multiple spaces from T-SQL string. It's nothing fancy or innovative - just sharing simple things that may not look that simple at first glance :)

declare @value nvarchar(50);
set @value = 'a      lot       of       multiple               spaces         here';

select replace(replace(replace(@value, ' ', '[]'), '][', ''), '[]', ' ') as CleanValue;

Of course this would really mess up your value if it already has '[' or ']' symbols in it. But the fix is really simple and straight forward - just replace the '[' and ']' symbols in the value with something unique before trimming the spaces and then put them back in. In my case, I'm replacing '[' and ']' with '|#L|' and '|#R|' respectively:

declare @value nvarchar(50);

set @value = 'a      lot       of       [multiple]               spaces         here';

select replace(replace(replace(replace(replace(replace(replace(@value, '[', '|#L|'), ']', '|#R|'), ' ', '[]'), '][', ''), '[]', ' '), '|#L|', '['), '|#R|', ']') as CleanValue;

For the sake of simplicity I'm using '|#L|' and '|#R|' to replace left and right square brackets. In working environ I'd use something more elaborate for the sake of my paranoic self - '|#_LSBRV-@|' for '[' and '|#_RSBRV-@|' for ']'

Cheers