Binaryworld - Working with Text dtatype (using READTEXT, WRITETEXT and TEXTPTR funct ... [ SQL Server -> BLOB (Text-Image) ]

A. Use TEXTPTR

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.

Click here to copy the following block

USE pubs
GO
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'
GO

B. Use TEXTPTR with in row text

In SQL Server 2000, the in row text pointer must be used inside a transaction. Here is an example.

Click here to copy the following block

CREATE TABLE t1 (c1 int, c2 text)
EXEC sp_tableoption 't1', 'text in row', 'on'
INSERT t1 VALUES ('1', 'This is text.')
GO
BEGIN TRAN
DECLARE @ptrval VARBINARY(16)
SELECT @ptrval = TEXTPTR(c2)
FROM t1
WHERE c1 = 1
READTEXT t1.c2 @ptrval 0 1
COMMIT

C.1 Get Pointer

This example selects the pub_id column and the 16-byte text pointer of the pr_info column from the pub_info table.

Click here to copy the following block

USE pubs
GO
SELECT pub_id, TEXTPTR(pr_info)
FROM pub_info
ORDER BY pub_id
GO

Here is the result set:

pub_id
------ ----------------------------------
0736 0x6c0000000000feffb801000001000100
0877 0x6d0000000000feffb801000001000300
1389 0x6e0000000000feffb801000001000500
1622 0x700000000000feffb801000001000900
1756 0x710000000000feffb801000001000b00
9901 0x720000000000feffb801000001000d00
9952 0x6f0000000000feffb801000001000700
9999 0x730000000000feffb801000001000f00

(8 row(s) affected)

C.2 Read Text Without Without ReadText

This example shows how to return the first 8,000 bytes of text without using TEXTPTR.
Is the size (an integer) of text data, in bytes. The maximum setting for SET TEXTSIZE is 2 gigabytes (GB), specified in bytes. A setting of 0 resets the size to the default (4 KB).

Click here to copy the following block

USE pubs
GO
SET TEXTSIZE 8000
SELECT pub_id, pr_info
FROM pub_info
ORDER BY pub_id
GO

Here is the result set:

pub_id pr_info
------ -----------------------------------------------------------------
0736 New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!
0877 This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.

This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washi
1389 This is sample text data for Algodata Infosystems, publisher 1389 in the pubs database. Algodata Infosystems is located in Berkeley, California.

9999 This is sample text data for Lucerne Publishing, publisher 9999 in the pubs database. Lucerne publishing is located in Paris, France.

This is sample text data for Lucerne Publishing, publisher 9999 in the pubs database. Lucerne publishing is located in

(8 row(s) affected)

D. Return specific text data

This example locates the text column (pr_info) associated with pub_id 0736 in the pub_info table of the pubs database. It first declares the local variable @val. The text pointer (a long binary string) is then put into @val and supplied as a parameter to the READTEXT statement, which returns 10 bytes starting at the fifth byte (offset of 4).

Click here to copy the following block

USE pubs
GO
DECLARE @val varbinary(16)
SELECT @val = TEXTPTR(pr_info)
FROM pub_info
WHERE pub_id = '0736'
READTEXT pub_info.pr_info @val 4 10
GO

Here is the result set:

(1 row(s) affected)

pr_info
------------------------------------------------------------------------
is sample

E. Write Text data in text field

This example puts the text pointer into the local variable @ptrval, and then WRITETEXT
places the new text string into the row pointed to by @ptrval.

Click here to copy the following block

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication.
With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO