SQL Textptr Code Library

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