REPLACE function in T-SQL 2008 is different from T-SQL 2005

Question

I am on project of migrating databases from SQL Server 2005 to 2008.

During test I found one inconsistency. In accordance to BOL http://msdn.microsoft.com/en-us/library/ms186862(v=SQL.100).aspx (2008) and http://msdn.microsoft.com/en-us/library/ms186862(v=SQL.90).aspx (2005) returns varchar. So far both are the same. However if we pass to REPLACE function column type char then difference comes out. Look at this code

declare @test table
(
  testcharstring char(25)
) 

insert into @test
  select 'Hello'
  union
  select 'World'
  union
  select 'Hello world '

select 
  '"'+testcharstring+'"' as original
  ,'"'+replace(testcharstring,'a','A')+'"' as afterreplace
  --,'"'+replace(rtrim(testcharstring),'a','A')+'"'
from @test

Result from SQL Server 2005

original                    afterreplace
--------------------------- ---------------------------
"Hello                    " "Hello"
"Hello world              " "Hello world"
"World                    " "World"

Result from SQL Server 2008

original                    afterreplace
--------------------------- ---------------------------
"Hello                    " "Hello                    "
"Hello world              " "Hello world              "
"World                    " "World                    "

T-SQL in SQL Server 2005 removes even legitimate trailing space, not to say that it threats char(25) as varchar(25). T-SQL in SQL Server 2008 approaches type more carefully and returns results in accordance of type which it receives for transformation

I have number places in different T-SQL objects, mostly in triggers. Main idea just to make minimal changes to keep same behaviour in SQL Server 2008

Possible ways to do it

• Override built-in REPLACE function Quick search suggests that it impossible however my teammate wants to research that option
• Use Rtrim() functions together with REPLACE. This will require replacement in exact places in code in multiple routines (where char columns are used)
• Creating own version Replace in CLR to see that CLR allows me to keep SQL Server 2005 behaviour and then again search and replace function in exact location

I would like to ask everybody if somebody came across of this issue, how did you worked out?

Also any suggestion is also welcome, may be I just do not know what settings on server instance or database level can change behaviour.

Thank you in advance!

Answer 1

You have different SET ANSI_PADDING options, which can also be controlled by SET ANSI_DEFAULTS

As it stands, REPLACE behaves the same in both editions. Both (2005, 2008) say:

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.

Edit: there are 2 Connect bugs/features

My answer above is probably wrong

http://connect.microsoft.com/SQLServer/feedback/details/259840/trailing-spaces-are-lost-when-a-char-value-is-fed-to-replace

Check DB compatible level:

http://connect.microsoft.com/SQLServer/feedback/details/126092/t-sql-replace-function-seems-to-be-broken-for-char-x-variables

And as a fix, sorry, I'd use rtrim, however is it a fix? You can't override replace, and if you plan on a clr urgent, why not wrap the replace/rtrim in a SQL udf