Text and image functions return values commonly needed for operations on text and image data only. Text and image built-in function names are not keywords.
function_name(parameters)
where
These are the text and image functions:
Function | Parameters | Result |
---|---|---|
TEXTPTR | (column_name) | The text-pointer value in varbinary format. The text pointer is checked to ensure that it points to the first text page. |
TEXTVALID | ('table_name.column_name', text_ ptr) | Checks whether a given text pointer is valid. Returns 1 if the pointer is valid and 0 if the pointer is invalid. Note that the identifier for the text column must include the table name. |
If a text or image column has not been initialized by an INSERT or UPDATE statement, TEXTPTR returns a null pointer. Use TEXTVALID to check whether a text pointer exists. You cannot use UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer.
In addition to these specific text and image functions, the following functions and statement are also useful with text and image data:
Function or statement | Description |
---|---|
PATINDEX('%pattern%', expression) |
The character position of a given character string in text columns. For details, see "String Functions" earlier in this topic. |
DATALENGTH(expression) | The length of data in text and image columns. For details, see "System Functions," earlier in this topic. |
SET TEXTSIZE | The limit, in bytes, of the text or image data to be returned with a SELECT statement. For details, see the SET statement. |
This example uses the TEXTPTR function to locate the image column logo associated with New Moon Books in the pub_info table of the pubs database. The text pointer is put into a local variable @ptrval.
DECLARE @ptrval varbinary(16) SELECT @ptrval = TEXTPTR(logo) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books'
This example selects the pub_id column and the 16-byte text pointer of the pr_info column from the pub_info table.
SELECT pub_id, TEXTPTR(pr_info) FROM pub_info
To return the first 255 bytes of text:
SELECT pub_id, pr_info FROM pub_info
This example reports whether a valid text pointer exists for each value in the logo column of the pub_info table.
SELECT pub_id, 'Valid (if 1) Text data' = TEXTVALID ('pub_info.logo', TEXTPTR(logo)) FROM pub_info