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
Published in: SQL
Download

Related snippets