TSQL: Updating Large-Value Data Types

Syntax:

UPDATE tablename
set column_name.WRITE(expression, @Offset, @Length)
[where <condition> ]

where:
expression - value to be written
@Offset - start point, zero-based, bigint
        NULL offset means perform an append to existing column value
@Length - length from offset to be replaced by the expression, bigint
        NULL length removes all data from the offset to the end of the column value

Sample:--------------------------------------------------------------
create table temp4
(textid int identity(1,1),
sampletext varchar(max))

insert into temp4(sampletext)
values('This is the day that the Lord has made. We will rejoice and be glad in it.')
---------------------------
update temp4
set sampletext.write(' Yes', null, null)
where textid = 1
Output:
This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
set sampletext.write('Love... love... love...', 0, 0)
where textid = 1

Output:
Love... love... love...This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
set sampletext.write('TRUE -', 23, 0)
where textid = 1

Output:
Love... love... love...TRUE -This is the day that the Lord has made. We will rejoice and be glad in it. Yes

---------------------------
update temp4
 set sampletext.write('Clearly', 69, null)
 where textid = 1

Output:
Love... love... love...TRUE -This is the day that the Lord has made. Clearly