Retrieving ntext, text, or image Values
Important
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use varchar(max), nvarchar(max), and varbinary(max) data types instead. For more information, see Using Large-Value Data Types.
You can retrieve ntext, text, or image values in the following ways:
By simply referencing the column in a SELECT statement.
Note
To run the examples in this topic, you will need to install the pubs and Northwind databases. For information about how to install these databases, see Downloading Northwind and pubs Sample Databases.
For example, the following query returns all information in the pr_info column for each publisher:
USE pubs
SELECT pr_info
FROM pub_info
This is the method used in a database application using an API such as ADO, OLE DB, or ODBC. The column is bound to a program variable, and then a special API function or method is used to retrieve the data one block at a time.
When this method is used in Transact-SQL scripts, stored procedures, and triggers, it works only for relatively short values. If the length of the data is longer than the length specified in SET TEXTSIZE, you must use increase TEXTSIZE or use another method. The current TEXTSIZE setting is reported by the @@TEXTSIZE function and is changed with the SET TEXTSIZE statement:
SET TEXTSIZE 64512
The default setting for TEXTSIZE is 4096 (4 KB). This statement resets TEXTSIZE to its default value:
SET TEXTSIZE 0
The full amount of data is returned if the length is less than TEXTSIZE.
By using the TEXTPTR function to get a text pointer that is passed to the READTEXT statement.
The READTEXT statement is used to read blocks of ntext, text, or image data. For example, the following query returns the first 25 characters (or first row) of the sample text data for each publisher:
USE pubs
DECLARE @textpointer varbinary(16)
SELECT @textpointer = TEXTPTR(pr_info)
FROM pub_info
READTEXT pub_info.pr_info @textpointer 1 25
By using the SUBSTRING function t retrieve a block of data starting at a specific offset from the start of the column.
For example, the following query returns the first 25 characters (or first row) of the sample text data for each publisher:
USE pubs
SELECT SUBSTRING(pr_info, 1, 25) AS pr_info
FROM pub_info
By using the PATINDEX function to retrieve offset of some particular pattern of bytes.
This value can then be used in a SUBSTRING function or READTEXT statement to retrieve the data. For example, this query searches for the string Germany in the pr_info column of the pub_info table and returns the starting position of 103 (the G of the string Germany begins at character 103 of the pr_info column):
USE pubs
SELECT PATINDEX('%Germany%', pr_info) AS pr_info
FROM pub_info
PATINDEX operates on text and character data types only; it does not accept image values.
Retrieving Parts of ntext, text, or image Values
These methods are not limited to retrieving the entire ntext, text, or image value starting with the first byte. The methods can be combined to provide flexible processing that retrieves different parts of the ntext, text, or image values. For example, the following SELECT statement retrieves whatever part of a text value is between a start tag and an end tag:
USE Northwind
GO
CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)
GO
INSERT INTO TextParts
VALUES( 1,
'Sample string START TAG What I want END TAG Trailing text.')
GO
SELECT SUBSTRING( ColB,
/* Calculate start as start of tag + tag length. */
(PATINDEX('%START TAG%', ColB) + 10),
/* Calculate SUBSTRING length as end - start. */
(
PATINDEX('%END TAG%', ColB) -
( PATINDEX('%START TAG%', ColB) + 10 )
)
)
FROM TextParts
GO
Here is the result set.
------------------------
What I want
(1 row(s) affected)
Note
When you are selecting image data, the returned value includes the characters 0x, which indicate that the data is hexadecimal. These two characters are counted as part of TEXTSIZE.
See Also
Other Resources
Data Types (Transact-SQL)
READTEXT (Transact-SQL)
SET TEXTSIZE (Transact-SQL)
SELECT (Transact-SQL)
Text and Image Functions (Transact-SQL)
BLOBs and OLE Objects
Managing Text and Image Columns