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; |
Read More
http://technet.microsoft.com/en-us/library/ms177523.aspx