Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005

    Unanswered: use replace() in an update in a large ntext

    I've got a table that I have to update in preparation for our environment move (2k to 2005 SP2). The developers that designed the application created a table called schemas, which holds the contents of an XML file inside of an ntext field named Data.

    I need to parse through the field and do a find/replace to replace all instances of with It's all over the place in the file. The problem is, that the datalength() of each of the fields (there are 2 rows) are above 15000.

    normally, I'd run something like this:
    update schemas 
    set data=replace (cast(Data as varchar(max)),'','') 
    where data like ''
    Smaller columns it works great - but it won't work on these because they're too big (the update will chop anything beyond the varchar(max) value). I could do it manually, but this DB will be refreshed from production on a weekly basis and I'd like to script as many of the environment changes to the DB as much as possible.

    Any ideas?

  2. #2
    Join Date
    Aug 2005
    never mind - the character limit return value in SQL Mgmt Studio was playing tricks on me (why can't I have a 20,000 character result-set in text view??).

    cast (data as varchar(max)) actually works fine

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts