SQL Server/T-SQL Tutorial/Data Types/Large Text

truncates the string at the 28th position

6> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL
7>   DROP TABLE dbo.CustomerData;
8> GO
1>
2> CREATE TABLE dbo.CustomerData
3> (
4>   custid      INT            NOT NULL PRIMARY KEY,
5>   txt_data    VARCHAR(MAX)   NULL,
6>   ntxt_data   NVARCHAR(MAX)  NULL,
7>   binary_data VARBINARY(MAX) NULL
8> );
9> GO
1> UPDATE dbo.CustomerData
2>   SET txt_data.WRITE(NULL, 28, 0)
3> WHERE custid = 102;
4> GO
1>
2> drop table CustomerData;
3> GO

truncates the string at the ninth position, and appends "102" at the end

4> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL
5>   DROP TABLE dbo.CustomerData;
6> GO
1>
2> CREATE TABLE dbo.CustomerData
3> (
4>   custid      INT            NOT NULL PRIMARY KEY,
5>   txt_data    VARCHAR(MAX)   NULL,
6>   ntxt_data   NVARCHAR(MAX)  NULL,
7>   binary_data VARBINARY(MAX) NULL
8> );
9> GO
1> UPDATE dbo.CustomerData
2>   SET txt_data.WRITE("102", 9, NULL)
3> WHERE custid = 102;
4> GO
1>
2> drop table CustomerData;
3> GO

Updating Large Value Data Type Columns

varchar(max), which holds non-Unicode variable length data.
nvarchar(max), which holds Unicode variable length data.
varbinary(max), which holds variable length binary data.
These data types can store up to 2^31-1 bytes of data.
For updating large value data types the UPDATE command now includes the .WRITE method:
UPDATE <table_or_view_name>
SET column_name = .WRITE ( expression , @Offset , @Length )
FROM <table_source>
WHERE <search_condition>
The parameters of the .WRITE method are described in following table.
Argument      Description
expression    The expression defines the chunk of text to be placed in the column.
@Offset       @Offset determines the starting position in the existing data the new text should be placed. If @Offset is NULL, it means the new expression will be appended to the end of the column (also ignoring the second @Length parameter).
@Length       @Length determines the length of the section to overlay.
Referenced from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach