use pubs
go
-- WRITETEXT with an unprotected text pointer.
declare @mytextptr varbinary(16)
select @mytextptr=TEXTPTR(pr_info)
from pub_info where pub_id='9999'
IF @mytextptr IS NOT NULL
WRITETEXT pub_info.pr_info @mytextptr WITH LOG 'Hello Again'
go
-- WRITETEXT with a properly protected text pointer.
BEGIN TRAN
declare @mytextptr varbinary(16)
select @mytextptr=TEXTPTR(pr_info)
from pub_info (UPDLOCK) where pub_id='9999'
IF @mytextptr IS NOT NULL
WRITETEXT pub_info.pr_info @mytextptr WITH LOG 'Hello Again'
COMMIT TRAN
go
-- READTEXT with a protected text pointer.
BEGIN TRAN
declare @mytextptr varbinary(16),@sizeneeded int, @pat_offset int
select @mytextptr=TEXTPTR(pr_info),@pat_offset=patindex('%Washington%',pr_info) - 1,
@sizeneeded=datalength(pr_info) - patindex('%Washington%',pr_info) - 1
from pub_info (HOLDLOCK) where pub_id='0877'
IF @mytextptr IS NOT NULL AND @pat_offset >= 0 AND @sizeneeded IS NOT NULL
READTEXT pub_info.pr_info @mytextptr @pat_offset @sizeneeded
COMMIT TRAN
go
-- READTEXT in a loop to read chunks of text.
-- Instead of using HOLDLOCK, use SET TRANSACTION ISOLATION LEVEL
-- REPEATABLE READ (equivalent). Then set it back when done but
-- be sure to do so in a separate batch.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TEXTSIZE 100 —- Just for illustration; too small for
—- real world. 4000 would be a better value.
BEGIN TRAN
DECLARE @mytextptr varbinary(16), @totalsize int,
@lastread int, @readsize int
SELECT
@mytextptr=TEXTPTR(pr_info), @totalsize=DATALENGTH(pr_info),
@lastread=0,
-- Set the readsize to the smaller of the @@TEXTSIZE setting
-- and the total length of the column
@readsize=CASE WHEN (@@TEXTSIZE < DATALENGTH(pr_info)) THEN
@@TEXTSIZE ELSE DATALENGTH(pr_info) END
FROM pub_info WHERE pub_id='1622'
IF @mytextptr IS NOT NULL AND @readsize > 0
WHILE (@lastread < @totalsize)
BEGIN
READTEXT pub_info.pr_info @mytextptr @lastread @readsize
IF (@@error <> 0)
BREAK -- Break out of loop if an error on read
-- Change offset to last char read
SELECT @lastread=@lastread + @readsize
-- If read size would go beyond end, adjust read size
IF ((@readsize + @lastread) > @totalsize)
SELECT @readsize=@totalsize - @lastread
END
COMMIT TRAN
GO
—- Set it back, but in a separate batch
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- UPDATETEXT to trim off the end.
declare @mytextptr varbinary(16),@pat_offset int
BEGIN TRAN
select @mytextptr=TEXTPTR(pr_info),
@pat_offset=patindex('%D.C.%',pr_info) - 1 + 4
-- for offset, subtract 1 for offset adjust but add 4 for length of "D.C."
from pub_info (UPDLOCK) where pub_id='0877'
IF @mytextptr IS NOT NULL AND @pat_offset >= 0
UPDATETEXT pub_info.pr_info @mytextptr @pat_offset NULL WITH LOG
COMMIT TRAN
go
-- UPDATETEXT to append to the end
declare @mytextptr varbinary(16)
BEGIN TRAN
SELECT @mytextptr=TEXTPTR(pr_info) FROM pub_info (UPDLOCK) WHERE pub_id='0877'
IF @mytextptr IS NOT NULL
UPDATETEXT pub_info.pr_info @mytextptr NULL NULL WITH LOG
"Mary Doe is president of the company."
COMMIT TRAN
go
-- UPDATETEXT to completely overwrite a text column. Alternative to WRITETEXT
declare @mytextptr varbinary(16)
BEGIN TRAN
SELECT @mytextptr=TEXTPTR(pr_info) FROM pub_info (UPDLOCK) WHERE pub_id='9999'
IF @mytextptr IS NOT NULL
UPDATETEXT pub_info.pr_info @mytextptr 0 NULL WITH LOG
'New text for 9999'
COMMIT TRAN
go
-- UPDATETEXT to insert characters
DECLARE @mytextptr varbinary(16)
BEGIN TRAN
SELECT @mytextptr=TEXTPTR(pr_info) FROM pub_info (UPDLOCK)
WHERE pub_id='0877'
IF @mytextptr IS NOT NULL
UPDATETEXT pub_info.pr_info @mytextptr NULL NULL WITH LOG
'Kimberly Tripp is president of the company.'
COMMIT TRAN
-- UPDATETEXT to insert vertical and horizontal tab
declare @mytextptr varbinary(16), @pat_offset int, @mystring char(2)
BEGIN TRAN
SELECT
@mystring= CHAR(13) + CHAR(9), -- Vertical tab is code point 13. Tab is 9.
@pat_offset=patindex('%Kim%',pr_info) - 1,
@mytextptr=TEXTPTR(pr_info) FROM pub_info (UPDLOCK) WHERE pub_id='0877'
IF @mytextptr IS NOT NULL AND @pat_offset >= 0
UPDATETEXT pub_info.pr_info @mytextptr @pat_offset 0 WITH LOG @mystring
COMMIT TRAN
go
-- UPDATETEXT for Search and Replace.
declare @mytextptr varbinary(16), @pat_offset int, @oldstring varchar(255), @newstring varchar(255), @sizeold int
BEGIN TRAN
SELECT @oldstring='Tripp',@newstring='Tripp-Simonnet'
SELECT @sizeold=datalength(@oldstring),
@pat_offset=patindex('%'+@oldstring+'%',pr_info) - 1,
@mytextptr=TEXTPTR(pr_info)
FROM pub_info (UPDLOCK) WHERE pub_id='0877'
IF @mytextptr IS NOT NULL AND @pat_offset >= 0
UPDATETEXT pub_info.pr_info @mytextptr @pat_offset @sizeold WITH LOG @newstring
COMMIT TRAN
go
-- UPDATETEXT to copy and append another text column
declare @target_textptr varbinary(16), @source_textptr varbinary(16)
BEGIN TRAN
SELECT @target_textptr=TEXTPTR(pr_info) FROM pub_info (UPDLOCK) WHERE pub_id='0877'
SELECT @source_textptr=TEXTPTR(pr_info) FROM pub_info (HOLDLOCK) WHERE pub_id='9952'
IF @target_textptr IS NOT NULL AND @source_textptr IS NOT NULL
UPDATETEXT pub_info.pr_info @target_textptr NULL NULL WITH LOG pub_info.pr_info @source_textptr
COMMIT TRAN
go
