Let's consider a simple table:
CREATE TABLE TempStuff
(
i INT IDENTITY(1, 1)
PRIMARY KEY ,
mytext VARCHAR(MAX) ,
myshorttext VARCHAR(1000)
)
Let's put some data in it:
INSERT dbo.TempStuff
( mytext, myshorttext )
VALUES ( 'This is text', -- mytext - varchar(max)
'This is text' -- myshorttext - varchar(1000)
)
SELECT * FROM dbo.TempStuff
So, let's say you want to insert a word into our text. There are several ways of doing it:
The "left-write-right" method:
UPDATE dbo.TempStuffSET mytext = LEFT(mytext, 7) + ' test' + RIGHT(mytext, LEN(mytext) - 7) ,
myshorttext = LEFT(myshorttext, 7) + ' test'
+ RIGHT(myshorttext, LEN(myshorttext) - 7)
SELECT * FROM dbo.TempStuff
This has simplicity, and is easy to understand for the new coder, but is not the only method. You can use the STUFF function:
UPDATE dbo.TempStuffSET mytext = STUFF(mytext, 9, 0, 'stuffed ') ,
myshorttext = STUFF(myshorttext, 9, 0, 'stuffed ')
SELECT * FROM dbo.TempStuff
However, I recently found a method I had been previously unaware of, specifically for the varchar(max), nvarchar(max), varbinary(max) types (it doesn't work on, for example, other varchar fields as they are not treated the same way within SQL server). This is the .WRITE method, used to put some text at the end of a string:
UPDATE dbo.TempStuff
SET mytext.WRITE(' written at the end',NULL,0)
SELECT * FROM dbo.TempStuff
The syntax is:
StringToUpdate.WRITE(Newtext,StartPosition,CharactersToDelete)
(if StartPosition is null, the end of the string to be updated is used)
Now, given the alternatives and limitations, why would you use .WRITE? Well, whereas other methods need to read the text, update it, and write it, this only needs change the updated data. This is important where you have large data, as putting text on the end of the column is minimally logged (if you are in bulk-logged or simple modes) which can be faster. This MSDN (see the section "Updating Large Value Data Types") notes that it is most effective if 8060 bytes are inserted at a time.
No comments:
Post a Comment