A bug in Microsoft SQL Server's replace() functionTue, Nov 15, 2005 in Databases
I have found a bug in Microsoft SQL Server’s replace() function. The function treats
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' else print 'Replacement failed'
Result: “Replacement failed.” This is clearly incorrect behavior. To make it more interesting, here are some test cases that succeed:
- Change the type of
- Change the value of @string to
' '(two spaces).
replicate(@replace, 2)instead of
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.