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 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

    

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