Updating Large Value Types with UPDATE .WRITE

Excerpt by Don Kiely | May 10, 2013

SQL Server provides an UPDATE .WRITE Transact-SQL statement to perform partial updates on columns that are defined as varchar(max), nvarchar(max), or varbinary(max). Here is the abbreviated syntax:

UPDATE { <object> }
{ column_name = { .WRITE ( expression, @Offset,
@Length ) }

The following example uses the Production.Document table in the AdventureWorks database to demonstrate substitution of the word "critical" for the word "important" in one row of data with a DocumentNode value of 0x5B40.

First, select the row to view the existing data:

SELECT DocumentSummary FROM Production.Document
WHERE DocumentNode = 0x5B40;

The first sentence in the results should look like the Figure below (you'll need to expand the width of the column to view the entire text of the column).

Updating Large Value Types with UPDATE .WRITE

To change the word "important" to the word "critical" you need to specify the offset (the number of characters from the start of the string to the value you want to replace) and the length of the string to replace.

UPDATE Production.Document
SET DocumentSummary .WRITE (N'critical',6,9)
WHERE DocumentNode = 0x5B40;

If you execute the SELECT statement again to view the current value, you'll see the results as shown in the Figure below.

Updating Large Value Types with UPDATE .WRITE

Execute the following statement to restore the original value:

UPDATE Production.Document
SET DocumentSummary .WRITE (N'important',6,8)
WHERE DocumentNode = 0x5B40;

You can also use the CHARINDEX and PATINDEX functions to calculate the offset location of the substring you want to change. The LEN function returns the number of characters in a string, so the following statement performs the same replacement of 'critical' for 'important'. (In the sample code file, these statements are wrapped in a transaction that is rolled back, so you don't have to worry about executing another UPDATE statement to undo the change.)

UPDATE Production.Document
SET DocumentSummary .WRITE (N'critical',
CHARINDEX(N'important', DocumentSummary) - 1,
WHERE DocumentNode = 0x5B40;

WARNING! In past versions of SQL Server, WRITETEXT was used to replace sections of long text values. SQL Server 2012 still supports this statement, but it has been deprecated and will be removed in a future version of SQL Server. Therefore, you should always use the new .WRITE clause of an UPDATE statement rather than using WRITETEXT.


Thumbnail for 566This post is an excerpt from the online courseware for our SQL Server 2012: T-SQL Working with Data course written by expert Don Kiely.

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted May 10, 2013 from the online courseware SQL Server 2012, Part 9 of 9: T-SQL Working with Data by Don Kiely