Text and Image Functions - TEXTVALID (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

A text, ntext, or image function that checks whether a specific text pointer is valid.

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Alternative functionality is not available.

Transact-SQL syntax conventions

Syntax

TEXTVALID ( 'table.column' ,text_ ptr )

Arguments

table
Is the name of the table that will be used.

column
Is the name of the column that will be used.

text_ptr
Is the text pointer to be checked.

Return Types

int

Remarks

Returns 1 if the pointer is valid and 0 if the pointer is not valid. Note that the identifier for the text column must include the table name. You cannot use UPDATETEXT, WRITETEXT, or READTEXT without a valid text pointer.

The following functions and statements are also useful when you work with text, ntext, and image data.

Function or statement Description
PATINDEX ( '%pattern%', expression ) Returns the character position of a specified character string in text and ntext columns.
DATALENGTH ( expression ) Returns the length of data in text, ntext, and image columns.
SET TEXTSIZE Returns the limit, in bytes, of the text, ntext, or image data to be returned with a SELECT statement.

Examples

The following example reports whether a valid text pointer exists for each value in the logo column of the pub_info table.

Note

To run this example, you must install the pubs database.

USE pubs;  
GO  
SELECT pub_id, 'Valid (if 1) Text data'   
   = TEXTVALID ('pub_info.logo', TEXTPTR(logo))   
FROM pub_info  
ORDER BY pub_id;  
GO

Here is the result set.

pub_id Valid (if 1) Text data   
------ ----------------------   
0736   1                        
0877   1                        
1389   1                        
1622   1                        
1756   1                        
9901   1                        
9952   1                        
9999   1                        
  
(8 row(s) affected)

See Also

DATALENGTH (Transact-SQL)
PATINDEX (Transact-SQL)
SET TEXTSIZE (Transact-SQL)
Text and Image Functions (Transact-SQL)
TEXTPTR (Transact-SQL)