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!

Solution 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

Solution 2

according to MS this is a correct behavior and SQL2005 had it wrong. in you code you are using Replace() not only as corection function (Find a pattern and replace with another pattern) but also as Trim() function (if nothing found at least trim the incoming value)

but this is wrong when you are working with Char(). the only reason to use Char() as data type is to preserve the values data length at all cost.(IMHO) as in you need to ensure that returning Value length is ALWAYS the same regardless of actual stored character count. this is important when you need to build some kind of structure using string concatenation as in fixed length file for output, and do not care to bother with data length checks or conversions. otherwise you might as well use varchar() or nvarchar()

http://msdn.microsoft.com/en-us/library/ms143359(v=sql.100).aspx

In SQL Server 2005, trailing spaces specified in the first input parameter to the REPLACE function are trimmed when the parameter is of type char. For example, in the statement SELECT '<' + REPLACE(CONVERT(char(6), 'ABC '), ' ', 'L') + '>', the value 'ABC ' is incorrectly evaluated as 'ABC'.

In SQL Server 2008, trailing spaces are always preserved. For applications that rely on the previous behavior of the function, use the RTRIM function when specifying the first input parameter for the function. For example, the following syntax will reproduce the SQL Server 2005 behavior SELECT '<' + REPLACE(RTRIM(CONVERT(char(6), 'ABC ')), ' ', 'L') + '>'.

Comments

[Tim Schmelter]

If you change char(25) to nchar(25) the result in 2005 will be the same as in 2008, i am interested in the reason myself.

[MatBailie]

+1 : It's easy to say "Version X behaves different from Version Y" and much harder to check that all of the server's settings are actually the same. But check you should; if you don't you can get alsorts of migration heaches (especially with strings).

[likhtin]

@gbn Thank you for link. I played with all setting but result same. Have to go with RTRIM

[ZygD]

@likhtin:yep, but you can use a SQL udf to wrap this. There are some issues as noted so you're working around some known bugs in SQL server