Geeks With Blogs

News
Employers
Soppa Group India
iSmart Panache Inc
R Systems Internationals Ltd
Technovate eSolutions Pvt Ltd
The contents of this blog are my personal opinion and do not represent in any way the view of my employer.
These postings are provided "AS IS" with no warranties, and confer no rights.

Google PR™ - Post your Page Rank with MyGooglePageRank.com

Narendra Tiwari

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/

 

Posted on Friday, November 21, 2008 9:49 AM | Back to top


Comments on this post: Manipulating ntext datatype in sql server

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Narendra Tiwari | Powered by: GeeksWithBlogs.net