SQL Server Performance Forum – Threads Archive

Replacing in a NTEXT field

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?

---

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

---

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

---

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?

---

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

WHERE LEN(MyColumnName) <= 5000

---

]]>