REPLACE() function - SQL 2008 v/s SQL 2005

This blog is intended for sharing an interesting difference I spotted between the functionality of REPLACE function in SQL 2008 & 2005 versions

Consider the following example

DECLARE @Test table

(

ID int identity(1,1),

Val char(10)

)

INSERT INTO @Test

SELECT 'cat' union all

select 'test' union all

select 'hi' union all

select 'hello'

select REPLACE(Val,' ','.')

FROM @Test

Run this in a SQL 2008 box and the result will be as follows

Now if you run this in sql 2005, see what happens below

The reason is in SQL 2005 REPLACE trims trailing spaces whereas in SQL 2008 it preserves the space characters.