Post reply

Replacing substring with a substring

  • September 15, 2010 at 9:49 am

    #103985

    I'm attempting to use the following query to update the substring %/themes/old/% with '%/themes/new/%'

    update bodyTextTable

    set bodyText = replace (bodyText, '%/themes/old/%', '%/themes/new%')

    But I am returned the following error -

    Msg 8116, Level 16, State 1, Line 15

    Argument data type ntext is invalid for argument 1 of replace function.

    So to get round the ntext issue, I have used executed the following...

    update bodyTextTable

    set bodyText = replace (bodyText, '%/themes/old/%', '%/themes/new%')

    But the expected changes are not reflected in the table.

  • September 15, 2010 at 10:02 am

    #1221485

    What is the datatype of the bodyTextTable.bodyText column?

    Edit: Is there supposed to be a difference between the two update statements?

    Edit2: is the '%' actually in the data, or are you using that as you would with the LIKE operator? (You can't do that...)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 15, 2010 at 10:04 am

    #1221488

    Use a SELECT to debug this

    SELECT bodytext

    , replace (bodyText, '%/themes/old/%', '%/themes/new%')

    from bodyTextTable

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • September 15, 2010 at 10:05 am

    #1221490

    WayneS (9/15/2010)

    What is the datatype of the bodyTextTable.bodyText column?

    Edit: Is there supposed to be a difference between the two update statements?

    Wayne,

    The datatype of the bodyTextTable.bodyText column is ntext.

    I'm guessing that the first update statement failed because of the replace function on the ntext datatype

    The "%" is a wildcard

  • September 15, 2010 at 10:13 am

    #1221497

    Steve Jones - Editor (9/15/2010)

    Use a SELECT to debug this

    SELECT bodytext

    , replace (bodyText, '%/themes/old/%', '%/themes/new%')

    from bodyTextTable

    Your select statement returns

    Msg 8116, Level 16, State 1, Line 18

    Argument data type ntext is invalid for argument 1 of replace function.

  • September 15, 2010 at 10:33 am

    #1221511

    I have resolved this with the following statement

    update bodyTextTable

    set bodyText = cast(replace(cast(bodyText as nvarchar(max)),'/themes/old/', '/themes/new/') as ntext)

    I had to cast ntext to nvarchar and cast it back to ntext after the replace.

    Wayne and Steve - thanks for pointers

  • September 15, 2010 at 11:02 am

    #1221540

    No problem - glad I could help.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 15, 2010 at 6:15 pm

    #1221809

    rabisco (9/15/2010)

    I have resolved this with the following statement

    update bodyTextTable

    set bodyText = cast(replace(cast(bodyText as nvarchar(max)),'/themes/old/', '/themes/new/') as ntext)

    I had to cast ntext to nvarchar and cast it back to ntext after the replace.

    Wayne and Steve - thanks for pointers

    Actually, you've just patched the problem. NTEXT has been deprecated and will go away in the future. You're using SQL Server 2005... why not just change the NTEXT column to NVARCHAR(MAX) and call it "fixed"? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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