SQL Server/T-SQL Tutorial/Data Types/Large Text

Материал из SQL эксперт
Перейти к: навигация, поиск

truncates the string at the 28th position

   <source lang="sql">

6> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL 7> DROP TABLE dbo.CustomerData; 8> GO 1> 2> CREATE TABLE dbo.CustomerData 3> ( 4> custid INT NOT NULL PRIMARY KEY, 5> txt_data VARCHAR(MAX) NULL, 6> ntxt_data NVARCHAR(MAX) NULL, 7> binary_data VARBINARY(MAX) NULL 8> ); 9> GO 1> UPDATE dbo.CustomerData 2> SET txt_data.WRITE(NULL, 28, 0) 3> WHERE custid = 102; 4> GO 1> 2> drop table CustomerData; 3> GO</source>


truncates the string at the ninth position, and appends "102" at the end

   <source lang="sql">

4> IF OBJECT_ID("dbo.CustomerData") IS NOT NULL 5> DROP TABLE dbo.CustomerData; 6> GO 1> 2> CREATE TABLE dbo.CustomerData 3> ( 4> custid INT NOT NULL PRIMARY KEY, 5> txt_data VARCHAR(MAX) NULL, 6> ntxt_data NVARCHAR(MAX) NULL, 7> binary_data VARBINARY(MAX) NULL 8> ); 9> GO 1> UPDATE dbo.CustomerData 2> SET txt_data.WRITE("102", 9, NULL) 3> WHERE custid = 102; 4> GO 1> 2> drop table CustomerData; 3> GO</source>


Updating Large Value Data Type Columns

   <source lang="sql">

varchar(max), which holds non-Unicode variable length data. nvarchar(max), which holds Unicode variable length data. varbinary(max), which holds variable length binary data. These data types can store up to 2^31-1 bytes of data. For updating large value data types the UPDATE command now includes the .WRITE method: UPDATE <table_or_view_name> SET column_name = .WRITE ( expression , @Offset , @Length ) FROM <table_source> WHERE <search_condition> The parameters of the .WRITE method are described in following table. Argument Description expression The expression defines the chunk of text to be placed in the column. @Offset @Offset determines the starting position in the existing data the new text should be placed. If @Offset is NULL, it means the new expression will be appended to the end of the column (also ignoring the second @Length parameter). @Length @Length determines the length of the section to overlay. Referenced from: SQL Server 2005 T-SQL Recipes A Problem-Solution Approach</source>