Post reply

I need Replace function in 2008 behaive like in 2005

  • August 8, 2011 at 9:44 am

    #241434

    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.Looks at this code

    declare @test-2 table (testcharstring char(25))

    insert into @test-2

    select 'Hello'

    union

    select 'World'

    union

    select 'Hello world ' //note trailisg space

    select

    '"'+testcharstring+'"' original

    ,'"'+replace(testcharstring,'a','A')+'"' afterreplace

    from @test-2

    Result from 2005

    original afterreplace

    --------------------------- ---------------------------

    "Hello " "Hello"

    "Hello world " "Hello world"

    "World " "World"

    Result from 2008

    original afterreplace

    --------------------------- ---------------------------

    "Hello " "Hello "

    "Hello world " "Hello world "

    "World " "World "

    T-Sql 2005 removes even legitimate trailing space, not to say that it threats char(25) as varchar(25). T-Sql 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 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

    - Creating own version Replace in CLR to see that CLR allows me to keep 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!

  • August 8, 2011 at 9:53 am

    #1365318

    I've just confirmed your findings on our servers. Works exactly as you prescribed with 2k5 and 2k8 (R2)

    I've done 1 extra test by putting the db in compat mode 90 and it "solved" the problem.

    But I'm pretty sure this is not the answer you wanted.

  • August 8, 2011 at 9:54 am

    #1365320

    interesting...

    it looks to me that SQL 2008 has the REPLACE function overloaded now...pass it CHAR...it returns CHAR. pass it VARCHAR, it returns VARCHAR.

    maybe SQL 2005 didn't have the same overloaded functionality.

    you can test that yourself..;. change your example table to varchar(25) and you get the kind of results you expect.

    as for the fix, i'm not sure yet...your own scalar replace might be a possibility.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 8, 2011 at 9:59 am

    #1365324

    My vote would be either compat mode change, or alter the code to use RTRIM instead.

  • August 8, 2011 at 12:05 pm

    #1365430

    I am thinking to go ahead with Rtrim().

    I will find trough syscomments and sys.columns every usage

    PATINDEX('replace(%'+sys.columns..name+'%)',syscomments.text) >0

    and then change only those particular usages.

    If anybody will come up with different solution I am all ears even after fact. I just want to know best way to handle it in future.

    Thanks again

  • August 8, 2011 at 1:41 pm

    #1365489

    The behaviour change is documented here in Books Online

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

    N 56°04'39.16"
    E 12°55'05.25"

  • August 8, 2011 at 2:54 pm

    #1365540

    SwePeso (8/8/2011)

    The behaviour change is documented here in Books Online

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

    Interesting - thanks for the link.

    Another fix on a previously known bug - that will have some people making changes to account for the now 'proper' functionality.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply