sql-server - 如何在 SQL Server 中调用变量的方法

Question

我在培训视频中看到了以下语法，我想知道这叫什么以及我们可以对变量使用哪些方法？

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

存储在变量中的值是'zzzzzzzzasdfasdf...'

Answer 1

update.write 语法并不是真正的方法。它是一种用于更新大型数据类型(如 nvarchar(max))的 TSQL 语法。

官方UPDATE documentation描述了如何使用 .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 thevalue of column_name is to be modified. expression replaces @Lengthunits starting from @Offset of column_name. Only columns ofvarchar(max), nvarchar(max), or varbinary(max) can be specified withthis clause. column_name cannot be NULL and cannot be qualified with atable name or table alias.

expression is the value that is copied to column_name. expression mustevaluate 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 incolumn_name is truncated at the specified @Offset.

@Offset is the starting point in the value stored in column_name atwhich expression is written. @Offset is a zero-based ordinal byteposition, is bigint, and cannot be a negative number. If @Offset isNULL, the update operation appends expression at the end of theexisting column_name value and @Length is ignored. If @Offset isgreater than the byte length of the column_name value, the DatabaseEngine returns an error. If @Offset plus @Length exceeds the end ofthe underlying value in the column, the deletion occurs up to the lastcharacter 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 cannotbe a negative number. If @Length is NULL, the update operation removesall data from @Offset to the end of the column_name value.

另见 Updating Large Data Types和 examples provided for updating large data types .