A bug in Microsoft SQL Server's replace() function

I have found a bug in Microsoft SQL Server’s replace() function. The function treats char and varchar differently, and replacements will fail in some cases. Here is a demonstration of the bug:

declare @string varchar(40),
    @find char(2),
    @replace char(1)
set @string = 'two  spaces'
set @find = '  '
set @replace = ' '
if replace(@string, @find, @replace) = 'two spaces'
    print 'Replacement worked'
    print 'Replacement failed'

Result: “Replacement failed.” This is clearly incorrect behavior. To make it more interesting, here are some test cases that succeed:

  1. Change the type of @find to varchar(2).
  2. Change the value of @string to '  ' (two spaces).
  3. Use replicate(@replace, 2) instead of @find.

I found another discussion about this on Google groups, but it’s incoherent, doesn’t demonstrate the problem clearly, and speculates about the internal causes rather than suggesting ways to avoid the behavior (pointless). So I count that as “not posted elsewhere” and offer it here.

I'm Baron Schwartz, the founder and CEO of VividCortex. I am the author of High Performance MySQL and lots of open-source software for performance analysis, monitoring, and system administration. I contribute to various database communities such as Oracle, PostgreSQL, Redis and MongoDB. More about me.