Introduction

Normally, applications which are developed these days contain data which are easily stored using the commonly used data-types like VARCHAR, INTEGER, etc. Very rarely you will come across details which will require you to define a table column of data-type TEXT. For example, if you are storing the write-up of some book or some help text, you might be forced to use TEXT data-type. Also, some large-size XML files which need to be parsed regularly can be stored in such a column.

If such a need arises, you might also sometimes need to do some manipulation for such columns. Traditional querying style for TEXT columns will work, but will degrade the performance for the application.SQL Server has provided some in-built functionality to query TEXT columns through the use of READTEXT, WRITETEXT and UPDATETEXT. All the above functions work with a valid pointer to the TEXT column, which can be obtained using TXTPTR (text_columnname). TEXTPTR returns a pointer to the TEXT column in the specified row or to the TEXT column in the last row returned by the query if more than one row is returned. This function returns pointer in a VARBINARY (16) variable. If a valid address (pointer) is not obtained, NULL is returned.

We will use a simple table with the following structure to show the manipulation of TEXT variables:

CREATE TABLE TEST (TEST_ID INT, TEST_DESC TEXT)

To see the actual address returned by TEXTPTR, execute the following query:

SELECT TEST_IS, TEXTPTR (TEST_DESC) FROM TEST

To obtain a valid pointer from this table, you must declare a VARBINARY(16) variable:

DECLARE @ptr VARBINARY (16)
SELECT @ptr = TEXTPTR (TEST_DESC) FROM TEST WHERE TEST_ID = 1

As NULL is returned when a valid pointer is not obtained, it is better to check for NOT NULL like:

IF @ptr IS NOT NULL
--do something

Also, check for a valid pointer returned by TEXTPTR can be done by using TEXTVALID (‘tablename.text_column’, TEXTPTR(text_column)). The following query will give 1 and 0 as output for text_column if it is a valid pointer and invalid pointer respectively.

SELECT TEST_ID, 'IsValid' = TEXTVALID ('TEST.TEST_DESC’, TEXTPTR(TEST_DESC)) FROM TEST

Here, TEST_DESC is the column name, whose data-type is TEXT.

READTEXT

After getting the valid pointer for the text column, the use of READTEXT, WRITETEXT and UPDATETEXT is possible.

READTEXT is used to read the value from the TEXT column. It is possible to get part of the TEXT data in the column by specifying the starting position and the number of characters to retrieve.

The syntax is:

READTEXT tablename.text_columnname @pointer start_position no_of_bytes

When the query below is executed,

READTEXT TEST.TEST_DESC @ptr 0 100

the result set includes the first 100 characters of the TEST_DESC column.

WRITETEXT

WRITETEXT is used to overwrite the contents of the TEXT column. Unlike READTEXT, you cannot specify the start_position and no_of_bytes to read. WRITETEXT is executed one row at a time. You can use it from within a cursor. Also WRITETEXT cannot be used on text, ntext, and image columns in views.

The following query will update the row

WRITETEXT TEST.TEST_DESC @ptr 'This is new text-value…'

Use WRITETEXT to update and replace an entire text, ntext, or image field.

UPDATETEXT

UPDATETEXT is more powerful as it has all the functionality of WRITETEXT with additional features. You can update part-of data from one TEXT column to another. Use UPDATETEXT to change only a portion of a text, ntext, or image column in place.

The syntax for UPDATETEXT is:

UPDATETEXT tablename.dest_text_column @dest_ptr
Start_position no_of_char_to_delete tablename.src_text_column @srcptr

We will add one more column TEST_DESC_NEW in our sample table, with TEXT data-type for testing copy of data using UPDATETEXT. By executing the following set of queries, the TEST_DESC_NEW column data will be updated with the data from TEST_DESC column. As 0 is used as the starting position, the data of TEST_DESC column is added in the beginning. And also, the existing data in TEST_DESC_NEW is also not deleted as 0 is used for number of characters to delete from destination column.

DECLARE @srcptr VARBINARY (16)
DECLARE @destptr VARBINARY (16)
SELECT @srcptr = TEXTPTR (TEST_DESC) FROM TEST WHERE TEST_ID = 1
SELECT @destptr = TEXTPTR (TEST_DESC_NEW) FROM TEST WHERE TEST_ID = 1
UPDATETEXT TEST.TEST_DESC_NEW @destptr
    0 0 TEST.TEST_DESC @srcptr 

Conclusion

Though the use of TEXT columns is very rare, it is very useful to store large data. There are some limitations of using TEXT data-type, which we should be knowing before using it:

• You can only use LIKE operator in the WHERE clause for TEXT columns.
• READTEXT, WRITETEXT and UPDATETEXT cannot be used in SELECT statements.
• You cannot declare or manipulate a variable of TEXT data-type in a function or stored-procedure. But you can have a

  TEXT variable as parameter.