That explains it nicely - and Peter Larsson (SwePeso) added pretty much the exact same example as this QotD in the comments.
It's still realistically a bug, though--even if CHAR(0) is treated as an empty string in a Windows collation, REPLACE() given an empty string as its second parameter should do nothing, not hang the connection!
I'd also second that this is a bug, because the following works nicely:
select replace(cast('Hello World' collate SQL_Latin1_General_CP1_CI_AS as varchar(50)), char(0), '')
select replace(cast('Hello World' collate Latin1_General_100_CI_AS as varchar(50)), char(0), '')
select replace(cast('Hello World' collate Latin1_General_CI_AS as varchar(50)), char(0), '')
I'm well aware that the question was about
varchar(max) (that's why I got it wrong)... but given only 11 characters tried it anyway.
BTW, collation Latin1_General_100_ is English (Canada) (and many others) in SQL Server 2008, while Latin1_General_ is English (Canada) for SQL Server 2000 and 2005; you may want to check out this document.