Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Manipulating Text Columns

By Vasant Raj,

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.

Total article views: 17489 | Views in the last 30 days: 10
 
Related Articles
FORUM

UPDATETEXT or WRITETEXT?

Need to replace entire contents of one field in one row; BOL is a little cryptic.

FORUM

UPDATETEXT

Hi, I'm brasilian and not speak english, but a need a help. In my SQL Server I create a Table named...

FORUM

updatetext writetext in full recovery mode

Hi Folks, has anyone experienced any problems using updatetext or writetext on an ntext field in ...

FORUM

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts with the column name specified.

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, or image pointer value conflicts wit...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones