append string to ntext field
-
I nee to write an update query that will append a short string to the existing contents of an ntext field. Can someone help with the syntax because I'm getting an error message about + being the incorrect operator.
Thanks
-
Try something like this, if your data limit does not cross 4000 characters.
create table x
(a ntext)
insert into x values ('www.sqlservercentral.com')
update x
set a = cast(a as nvarchar(4000)) + ' is a good website'
select * from x
-
Check updatetext in bols.
Why are you using an ntext datatype??
-
What happens if some rows have more than 4000 characters in that column?
-
Uh, uh..., I know. It cuts off the rest of the characters and you are @#$%. Right?!
(You may want to consider adding a new Column to your table that is varchar(8000) and working through ALTERING column names so that the new column has the name the old column... This will prevent any further problems with nvarchar and appending... Of course, I am "assuming" this change will not harm any other processes you have already in place...).
I wasn't born stupid - I had to study.
-
I'm still wondering why he's using ntext instead of nvarchar... I know my users and they don't like to type 3-4 pages of paragraphs just for the fun of it.
-
To use UPDATETEXT to append text, use the following template:
DECLARE @tptr varbinary(16)
DECLARE @textToAppend nvarchar(4000)
SET @textToAppend = N'your text'
SELECT @tPtr = TEXTPTR(columnName)
FROM yourTable
WHERE ... -- this must return only one row
UPDATETEXT yourTable.columnName @tPtr NULL 0 @textToAppend
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply