Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 10 Using the Built-In Functions in Queries

Text functions used for text and image data

Examples of using text functions

This example uses the textptr function to locate the text column, copy, associated with title_id BU7832 in table blurbs. The text pointer, a 16-byte binary string, is put into a local variable, @val, and supplied as a parameter to the readtext command. readtext returns 5 bytes starting at the second byte, with an offset of 1.

declare @val binary(16) 
select @val = textptr(copy) from blurbs 
where au_id = "486-29-1786" 
readtext blurbs.copy @val 1 5

textptr returns a 16-byte varbinary string. Sybase suggests that you put this string into a local variable, as in the preceding example, and use it by reference.

An alternative to using textptr in the preceding declare example is the @@textptr global variable:

readtext texttest.blurb @@textptr 1 5

The value of @@textptr is set from the last insert or update to any text or image field by the current Adaptive Server process. Inserts and updates by other processes do not affect the current process.

Explicit conversion using the convert function is supported from text to char, nchar, unichar, varchar, univarchar, or nvarchar, and from image to varbinary or binary, but text or image data is truncated to the server page size. Conversion of text or image to datatypes other than these is not supported, implicitly or explicitly.


	Expand Search