We have a table with ntext type of field in sql server database, that actually contains text data.
ntext is used to store the unicode type of data that that takes 2 times storage size in bytes, is two times the number of characters entered.
Now the scenario is we would like to replace all occurances of a paricular string from the data with another string.
It is very simple to do with Replace command in Sql Server, but the constraint is it only takes varchar/nvarchar type of parameter i.e. we can not run Replace command over ntext, we need to cast/convert ntext into varchar/nvarchar but in that case there are chances of loss of data it can truncate the data while convering ntext into varchar/nvarchar.
UPDATETEXT is the suitable command to achieve this for ntext, it also include other binary type of data type image and text.
Following link is demonstration of UDDATETEXT for ntext datatype:
http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html
Above scenario is for SQL Server 2k only. If we have Sql Server 2k5, we should use varchar(max) and nvarchar(max) for text and ntext.
http://sqltips.wordpress.com/2007/05/28/use-varcharmaxnvarcharmax-instead-of-text-ntext/