I was wondering how to manipulate ntext datatype in stored procedure of SQL Server 2008. We have a column of type ntext in a table. We have to fetch data from that column, parse the data, change and then store it back. For all of the above task we have to use one or more than on stored procedure/function. So data passing between stored procedures are also involved.
Asked
Active
Viewed 5,071 times
3
-
1There had better be a **good** reason not to have converted to `nvarchar(max)`, since `ntext` is [deprecated](http://msdn.microsoft.com/en-us/library/ms187993.aspx). Care to share the reason? – Damien_The_Unbeliever Dec 14 '11 at 15:35
-
@Damien_The_Unbeliever , I didnt check what nvarchar(max) was capable of. If it can hold the data as long as that of ntext then I can go in and change the data type of the columns to nvarchar(max) – Rahatur Dec 14 '11 at 15:44
1 Answers
7
If you're in the position to change the schema, consider changing the data type from ntext to nvarchar(max). The later is new in SQL Server 2005, it's more efficient, and it works with string functions.
If you can't change the schema, convert the ntext to a local variable of type nvarchar(max). String functions do work with nvarchar(max). Example:
declare @txt nvarchar(max)
select @txt = NTextField from YourTable where id = @ID
... process @txt ...
update YourTable set NTextField = @txt where id = @ID
Andomar
- 232,371
- 49
- 380
- 404
-
you mean nvarchar(max) can handle data longer than 8000 in length? is there any max limit for nvarchar(max)? – Rahatur Dec 14 '11 at 15:37
-
2[max indicates that the maximum storage size is 2^31-1 bytes.](http://msdn.microsoft.com/en-us/library/ms186939.aspx) That's 2GB. – Andomar Dec 14 '11 at 15:47
-
`(max)` was introduced in [2005](http://msdn.microsoft.com/en-us/library/ms186939(v=SQL.90).aspx), not 2008. – Damien_The_Unbeliever Dec 14 '11 at 18:36
-