How to call methods on a variable in SQL Server

user_14348456 0
Viewed 59

I saw the below syntax in a training video and I was wondering what this is called and which methods can we use on a variable?

 DECLARE @text VARCHAR(max);
 SET @text = REPLICATE(CAST('asdfasdf' as varchar(max)),8000);
 SET @text.write('zzzzzzzz',0,8);
 SELECT @text

the value stored in the variable is 'zzzzzzzzasdfasdf...'

sql-server variables
2 Answers

The update.write syntax is not really a method. It is a TSQL syntax for updating large data types like nvarchar(max).

The official UPDATE documentation describes how to use .WRITE.

  SET  
        { column_name = { expression | DEFAULT | NULL }  
          | { udt_column_name.{ { property_name = expression  
                                | field_name = expression }  
                                | method_name ( argument [ ,...n ] )  
                              }  
          }  
          | column_name { .WRITE ( expression , @Offset , @Length ) }

.WRITE (expression,@Offset,@Length) Specifies that a section of the value of column_name is to be modified. expression replaces @Length units starting from @Offset of column_name. Only columns of varchar(max), nvarchar(max), or varbinary(max) can be specified with this clause. column_name cannot be NULL and cannot be qualified with a table name or table alias.

expression is the value that is copied to column_name. expression must evaluate to or be able to be implicitly cast to the column_name type. If expression is set to NULL, @Length is ignored, and the value in column_name is truncated at the specified @Offset.

@Offset is the starting point in the value stored in column_name at which expression is written. @Offset is a zero-based ordinal byte position, is bigint, and cannot be a negative number. If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored. If @Offset is greater than the byte length of the column_name value, the Database Engine returns an error. If @Offset plus @Length exceeds the end of the underlying value in the column, the deletion occurs up to the last character of the value.

@Length is the length of the section in the column, starting from @Offset, that is replaced by expression. @Length is bigint and cannot be a negative number. If @Length is NULL, the update operation removes all data from @Offset to the end of the column_name value.

See also Updating Large Data Types and the examples provided for updating large data types.

user_279680

It's not a method, though it sure looks like it. Unlike for example XML methods, which you can use in a SELECT, this is a mutator. It's available on (n)varchar and varbinary data types with max length specified, and is used to modify the stored values efficiently.

There's not much information about them out there, I only found this blog post.

user_31229210
Related