Thursday, March 25, 2010

Functionality Change in REPLACE Function in SQL Server 2008

What is the output for the following query?

DECLARE @V CHAR(6)

SET @V = 'ABC'

SELECT REPLACE(@V,' ','L')

Guess what you have two answers. In SQL Server 2005, it will be ABC while in SQL Server 2008 it is ABCLLL.

What does this mean? They have fixed the bug in REPLACE function in SQL Server 2008. In SQL Server 2005, for char data type, before replacing it trims the variable which is incorrect. In SQL Server 2008 now it is not trimming your data before the replacing function.

If you are in the process of upgrading to SQL Server 2008 this is an point to consider.

2 comments:

  1. Just you clarify - that's a space (' '), as the second argument to the REPLACE function, correct? In my browser, it kind of looks a bit like an empty string.

    ReplyDelete
  2. Very interesting fix: yes, Ray, it is a space, as below, and not just an empty string. And yes: I have 2005 and 2008 both installed on the same machine and the change is exactly as described! Of course, if the type is changed to VARCHAR(6) instead of CHAR(6) then the result is 'ABC' in each case.

    DECLARE @V CHAR(6)
    SET @V = 'ABC'
    SELECT REPLACE(@V,' ','L')

    ReplyDelete