Systems Engineering and RDBMS

Update text/ntext or varchar(max) data-type columns in SQL Server

Posted by decipherinfosys on July 24, 2007

Sometimes, there is a need to store large data set like a big xml file, resumes, book reviews etc. in the database. Since we store the data, occasionally we may have to update it as well. MS SQL Server 2000 provides TEXT data type to store such large contents in the table. In the following example, we will show how we can manipulate such columns. We will also demonstrate how we can perform similar string manipulation for columns with VARCHAR(MAX) data type introduced in MS SQL Server 2005.

We cannot update TEXT column using regular DML statements instead we need to use READTEXT (to read the value), WRITETEXT (to replace the existing value) and UPDATETEXT (to update the partial value). For detailed syntax of all the commands, refer to BOL.

First let us start with creating table and populate with sample data to test.

CREATE TABLE dbo.TEST
(
TEST_ID INT IDENTITY(1,1) NOT NULL,
TEST_TEXT TEXT,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
– FileGroup clause
;

INSERT INTO TEST(TEST_TEXT) VALUES(‘This is test message’);

In order to read, write or update TEXT column, we need to first obtain valid pointer to the TEXT column using TEXTPTR command. If query returns more than one row, than value of pointer will be of the last row fetched.

READTEXT
Following is the code snippet to read the text column.

– since textptr returns 16 byte binary string, we need to declare local variable.
DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(TEST_TEXT)
FROM dbo.TEST
WHERE test_id = 1

READTEXT test.test_text @ptr 8 5

Above command will return string of 5 bytes(size) starting from 9th byte(offset) because offset is 0 byte ordinal position. (0th character in case data type is ntext). Output of above command will be ‘test ‘.

WRITETEXT
Following is the code snippet to replace the column value.

DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(TEST_TEXT)
FROM dbo.TEST
WHERE test_id = 1

– This will replace the text with new text
WRITETEXT test.test_text @ptr ‘Hello World!’

Since command replaces the entire string it doesn’t need any offset value. It just requires valid pointer to the row. Execute select statement to see the updated string. It should return ‘Hello World!’.

UPDATETEXT
Following is the code snippet to update portion of the string of the column. We will replace ‘Wor’ with ‘Arno’ so that result will be ‘Hello Arnold!’

DECLARE @ptr varbinary(16)
SELECT @ptr = TEXTPTR(TEST_TEXT)
FROM dbo.TEST
WHERE test_id = 1

UPDATETEXT test.test_text @ptr 6 3 ‘Arno’

Updatetext requires insert_offset (to insert data from specified position) and delete_length(length of data to be deleted starting from insert_offset position). In our example, 6 is the insert_offset and 3 is delete_length. Selecting data should return string ‘Hello Arnold!’.

.WRITE (To manipulate VARCHAR(MAX) Columns)
In MS SQL Server 2005, Microsoft introduced three new data types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) to store the large data sets. This will replace TEXT, NTEXT and IMAGE data types in future releases. To manipulate columns defined with this datatype, Microsoft also introduced the .WRITE clause. Using this clause we can replace entire column (WRITETEXT) or update portion of the column (UPDATETEXT).

Microsoft strongly suggests to use VARCHAR(MAX) and related datatypes as they are going to remove TEXT, NTEXT and IMAGE datatypes and related functions (UPDATETEXT, WRITETEXT and READTEXT) from future releases.

In following example, we will see how we can perform string manipulation for columns defined with VARCHAR(MAX) data type. Let us add new column to table.

ALTER TABLE TEST ADD TEST_MAX VARCHAR(MAX);

Now let us try to use the .WRITE clause to update newly added column.

UPDATE test
SET test_max.write(‘This is test data’,0,17) WHERE test_id = 1
GO

Above statement will give following error.

Msg 5302, Level 16, State 1, Line 1
Mutator ‘write()’ on ‘TEST_MAX’ cannot be called on a null value.

Note the error above. The .WRITE clause cannot be used to modify NULL column. First we need to update it with temporary data and later on using .WRITE clause we can update it with correct value.

UPDATE test
SET test_max = ‘This is test data’ WHERE test_id = 1
GO

Now update column with actual value.

UPDATE test
SET test_max.write(‘Hello World!’,0,len(test_max)) WHERE test_id = 1
GO

.WRITE clause takes three arguments. New String, offset and length. In our case, 0 is the offset (Starting position) and len(test_max) is the length. We are replacing entire string with new string ‘Hello World!’.

Now to update the portion of the string, execute following command.

UPDATE test
SET test_max.write(‘Arno’,6,3) WHERE test_id = 1
GO

Above command replaces the 3 bytes starting from Byte 7(Since offset is 0 based ordinal position). Here we are replacing word ‘Wor’ with ‘Arno’. Executing SELECT * FROM TEST will give us the output ‘Hello Arnold!’.

Using the .WRITE clause we can also add string at the end of the existing string, remove portion of the string and/or removing data from the specified position to the end of the string. For achieving similar functionality for regular VARCHAR columns, we need to use the STUFF command.

About these ads

2 Responses to “Update text/ntext or varchar(max) data-type columns in SQL Server”

  1. [...] Here is a blog post about UPDATE .WRITE… [...]

  2. [...] Dopiero dzisiaj zauważyłem, że w SQL mamy coś takiego! Jest to metoda pozwalająca na dokonywanie zapisu do koluny nvarchar(max) w taki sposób, aby zmienić część tekstu na inny. Poniżej prezentuję przykład wzięty z http://decipherinfosys.wordpress.com/2007/07/24/update-textntext-or-varcharmax-data-type-columns-in-… [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: