Some days ago I posted a solution for a simple problem on a forum about to delete multiple spaces in one statement (not using loop). My suggestion was

declare @s varchar(100)

set @s = 'xxxx                                  yyyyy          zzzzzz'

SELECT REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(2)), CHAR(2) + ' ', ''), CHAR(2), '')


I used CHAR(2) because that is not commonly used in normal texts. I then thought I could use CHAR(0) to be on the "safe" side, and now strange things begun to happen.

Run this on your own risk, as you will see soon.

Select  q,
        len(q)
from    (
            SELECT    REPLACE(REPLACE(REPLACE(@s, ' ', ' ' + CHAR(0)), CHAR(0) + ' ', ''), CHAR(0), '') as q
        ) AS d


After some investigation there was a follow up with another twist of same problem.
As before, run this on your own risk. Your query window might go into an infinite loop.

select replace(cast('x' as varchar(max)), char(0), '')


Not all get this behaviour. It seems it is dependent on which collation you use, as shown here

-- Success
select replace(cast('x' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(max)), char(0), '');

-- Hangs
select replace(cast('x' COLLATE Latin1_General_CI_AS as varchar(max)), char(0), '');


And it doesn't happen for VARCHAR(n) nor NVARCHAR(MAX).

Read more about this on Connect here
https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=125502