SQL Server update .write clause -

SQL Server .write clause enables us to perform a full or partial update on columns that has data type of varchar, nvarchar, and varbinary. .Write clause has three arguments as ColumnName.WRITE (expression, @Offset, @Length). Column name cannot be NULL and we cannot use an alias. @Offset is the starting point of update and @length of the section in the column starting from @Offset the both takes NULL, 0 or positive integer.
Let us create a sample table and value to try all our cases

if exists( select 1 from information_schema.tables where table_name = 'table1')
	drop table table1
create table table1
( col1 int,
  col2 nvarchar(max)
);
go
insert into table1 values (1,N'States fo');
--check column value
select * from table1;

If @Offset is NULL the update operation append at the end of the existing value

--1, If @Offset is NULL 
update table1 set col2.write(N' America',null,0)
where col1=1;
 
--check column value
select * from table1;

Else If @Offset is 0(zero) the update operation append at the beginning of the existing value

--2, If @Offset is 0(zero) 
update table1 set col2.write(N'United ',0,0)
where col1=1;
 
--check column value
select * from table1;

Else If @Offset is positive integer the update operation append at the n(@Offset) character of existing value
–3, If @Offset is positive integer

update table1 set col2.write(N'of',14,2)
where col1=1;
 
--check column value
select * from table1;

Else If @Offset is negative Error (Negative offset or length in write.)

--4, If @Offset is negative 
update table1 set col2.write(N'of',-1,2)
where col1=1;

@Length is 0 insert the new value at @Offset character.

--1,@Length is 0 
update table1 set col2.write(N'Colorado ',0,0)
where col1=1;
 
--check column value
select * from table1;

@Length is positive number then replace everything from @Offset to @Offset + @Length.

--2,@Length is positive number 
update table1 set col2.write(N',',2,6)
where col1=1;
 
--check column value
select * from table1;

@Length is NULL then replace everything from @Offset to the end of the existing value.

--3,@Length is NULL 
update table1 set col2.write(N' Denver',4,NULL)
where col1=1; 
 
--check column value
select * from table1;

@Length is Negative number Error (Negative offset or length in write.)

--4,@Length is Negative number  
update table1 set col2.write(N' Denver',4,-1)
where col1=1;