Retrieving ntext, text, or image Values

You can retrieve ntext, text or image values by:

• Simply referencing the column in a SELECT statement.

  For example, this 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, ODBC, or DB-Library. 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.

  The DB-Library API also supports a dbtextsize parameter that controls the length of ntext, text, and image data that can be selected. The Microsoft OLE DB Provider for SQL Server and the SQL Server ODBC driver automatically set @@TEXTSIZE to its maximum of 2 GB.

• 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, this 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
  

• Using the SUBSTRING function to retrieve a block of data starting at a specific offset from the start of the column.

  For example, this 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
  

• 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, this 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

READTEXT

SET TEXTSIZE

SELECT

Text and Image Functions

BLOBs and OLE Objects

Managing Long Data Types

Managing text and image Columns