Text and Image Functions

Text functions perform operations on text, image, and unitext data. Use the set textsize option to limit the amount of text, image, and unitext data that is retrieved by a select statement.

Note: You can also use the @@textcolid, @@textdbid, @@textobjid, @@textptr, and @@textsize global variables to manipulate text, image, and unitext data.

For example, use 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. readtext provides a way to retrieve text, unitext, and image values if you want to retrieve only a portion of a column’s data.

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. SAP 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
Note: You can also use the string functions patindex and datalength on text, image, and unitext columns.