SQL Server Performance

Replacing in a NTEXT field

Discussion in 'T-SQL Performance Tuning for Developers' started by sql777, Feb 1, 2003.

  1. sql777 New Member

    I'm trying to remove all <br> tags from a ntext field using the replace function and QA complained about it.

    How do I do this?

    I tried:

    update TableName
    set body = replace(body,'<br>','<br>')


    ideas?
  2. trifunk New Member

    Unfortunately I don't think you can use the REPLACE function on text and ntext data types.

    What you can do is use the CAST function to temporarily cast the column to a varchar but it's tricky because there's a limit on the size of a varchar.

    UPDATE MyTableName
    SET MyColumnName =
    REPLACE(CAST(MyColumnName as varchar(5000)), '<br>,'')

    If you ntext field is greater than 5000 though you'd probably truncate your data though so be carefull.

    Cheers
    Shaun


    World Domination Through Superior Software
  3. satya Moderator

    Also refer to READTEXT/WRITETEXT/UPDATETEXT in books online to deal with Image/Text/Ntext column types.

    HTH

    Satya SKJ
  4. sql777 New Member

    So to be safe I guess I can check the size of the column..and if its whithin the varchar max length THEN do the replace on it?

    Can someone script that for me?
  5. arb New Member


    UPDATE MyTableName
    SET MyColumnName =
    REPLACE(CAST(MyColumnName as varchar(5000)), '<br>,'')

    WHERE LEN(MyColumnName) <= 5000


Share This Page