So most of my blog posts are “inspired” by questions that gets asked to me either in my day to day work or from emails I get from friends (usually developers hehehe) who need a quick answer on how to do something “SQL” related.
So my blackberry started buzzing at 11:30 pm one evening… well twice actually. The first buzz was a
“Are you still awake” msg and the second one was
“When you get up… in TSQL how do I do ntext_field1 + ntext_field2 as newfield?”
being 1/2 asleep at the time I decided I’d leave it till morning cause my wife always gives me hell when I email in bed LOL So the next morning I get up and start writing an email on string concatenation… then I reread his original questions and it wasn’t just a question on string concatenation it was about ntext concatenation.
so I gave the BOL link to UPDATETEXT and gave him a quick example to work with:
CREATE TABLE tblSQLServerPediaMembers (id INT NOT NULL, SomeTextValue NTEXT NULL)
INSERT tblSQLServerPediaMembers(id, SomeTextValue)
VALUES (1, 'How to Concatenate an ntext column with another')
SELECT * FROM tblSQLServerPediaMembers
DECLARE @ptr VARBINARY(16)
DECLARE @offset INT
SELECT @ptr = TEXTptr(SomeTextValue), @offset = DATALENGTH(SomeTextValue)
FROM tblSQLServerPediaMembers
WHERE id = 1
UPDATETEXT tblSQLServerPediaMembers.SomeTextValue @ptr NULL 0 '... now leave me alone LOL'
GO
SELECT * FROM tblSQLServerPediaMembers
GO
DROP TABLE tblSQLServerPediaMembers
At the end of it all I remember him telling me about a new app he was working on so I also mentioned that if he was on SQL 2005 or SQL 2008 that he should replace his ntext with nvarchar(max) because ntext is being deprecated and that nvarchar(max) can use “normal” string concatenation instead of needing to use the UPDATETEXT command.
CREATE TABLE tblSQLServerPediaMembers (id INT NOT NULL, SomeTextValue NVARCHAR(MAX) NULL)
INSERT tblSQLServerPediaMembers(id, SomeTextValue)
VALUES (1, 'How to Concatenate an ntext column with another')
SELECT * FROM tblSQLServerPediaMembers
UPDATE tblSQLServerPediaMembers
SET SomeTextValue = SomeTextValue + '... now leave me alone LOL'
GO
SELECT * FROM tblSQLServerPediaMembers
GO
DROP TABLE tblSQLServerPediaMembers
I didn’t hear from him again this weekend so I think he got what he needed. So for those of you still on SQL Server 2000 UPDATETEXT is your string concatenation friend and once you’re >= SQL Server 2005 you can forget all about UPDATETEXT (well until your blackberry starts vibrating at 11:30 at night hehehe)
Enjoy!!
Thanks man! It would of done the trick, but I had to re-evaluate my fix and had to go another route. Forgot that some other are running on the same app and it would affect them tremendously.
It looks like you declare and set an @offset variable but leave the value of the insert_offset parameter = NULL when you call UPDATETEXT. The end result is the same but some may find the example confusing.