LearnNowOnline

Blog

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> }

SET

{ 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).

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.

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,

LEN(N'important'))

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.

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