Saturday, February 20, 2010

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.