Search This Blog

Updating the text fields

-- CREATE TABLE TextExample (i int identity(1,1), text1 text, text2 text, text3 text)
-- INSERT INTO TextExample SELECT REPLICATE('a',7998), REPLICATE('b',7998), NULL



DECLARE @txtPtr1 Varbinary(16)
DECLARE @txtPtr2 Varbinary(16)
DECLARE @txtPtr3 Varbinary(16)

SELECT @txtPtr1 = TEXTPTR(text1)
FROM TextExample

SELECT @txtPtr2 = TEXTPTR(text2)
FROM TextExample

UPDATE TextExample
SET Text3 = Text1
WHERE i = 1


SELECT @txtPtr3 = TEXTPTR(text3)
FROM TextExample
WHERE i =1

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 ' '

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1

UPDATETEXT TextExample.Text3 @txtPtr3 NULL 0 TextExample.Text2 @txtPtr2

SELECT DATALENGTH(text3)
FROM TextExample
WHERE i =1









UPDATETEXT REPLACE TEXT

CREATE TABLE test (i int identity(1,1), j int, t text, c char(2))
go


delete test


INSERT INTO Test SELECT 1, 'asadf', 'I'
INSERT INTO Test SELECT 1, 'jjjjj', 'A'
INSERT INTO Test SELECT 1, 'kkkkksadf', 'A'
DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 1, @t, 'A'

INSERT INTO Test SELECT 2, 'asadf', 'I'
INSERT INTO Test SELECT 2, 'jjjjj', 'A'
INSERT INTO Test SELECT 2, 'kkkkksadf', 'A'
--DECLARE @t varchar(8000)
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = space(6000) + 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'
SELECT @t = 'sadf'
INSERT INTO Test SELECT 2, @t, 'A'






DECLARE @old varchar(20) ,
@new varchar(20)
SELECT @old = 'adf' ,
@new = 'qsd'

DECLARE @i int

DECLARE @txtPtr binary(16) ,
@offset int ,
@dellen int

SELECT @dellen = len(@old)
SELECT @txtPtr = 0

WHILE @txtPtr IS NOT NULL
BEGIN
SELECT @txtPtr = null
SELECT TOP 1 @i = i, @txtPtr = textptr(t)
FROM test
WHERE t LIKE '%' + @old + '%'

IF @txtPtr IS NOT NULL
BEGIN
SELECT @offset = 1
WHILE @offset <> 0
BEGIN
SELECT @offset = patindex('%' + @old + '%', t)
FROM test
WHERE i = @i
--SELECT offset = @offset

IF @offset > 0
BEGIN
SELECT @offset = @offset-1
UPDATETEXT test.t @txtPtr @offset @dellen @new
END
END -- end while
END-- End if
END-- end while








-- Update the text field with a varchar column


-- create table table1 ( id int identity, Fields1 text, status bit )
-- create table table2 (id int identity, Fields2 varchar(255),flag_field bit)

-- insert into table1 SELECT replicate('a',20),1
-- insert into table1 SELECT replicate('b',20),1
-- insert into table1 SELECT replicate('c',20),1
-- insert into table1 SELECT replicate('d',20),1
-- insert into table1 SELECT replicate('e',20),1
-- insert into table1 SELECT replicate('f',20),1
-- insert into table2 select ' and b',1
-- insert into table2 select ' and c' ,0
-- insert into table2 select ' and d' ,1
-- insert into table2 select 'and no',1

SET NOCOUNT ON

BEGIN TRAN
SELECT * from table1

DECLARE @Fields2 varchar(255)
DECLARE @flag_field bit

DECLARE @txtPtr1 Varbinary(16)
set @txtPtr1 = 0
IF OBJECT_ID('tempdb..#tabe') IS NOT NULL
DROP TABLE #tabe
CREATE TABLE #tabe (i int )
declare @id int

INSERT INTO #tabe
SELECT table1.id
FROM table2, table1
WHERE table1.id = table2.id and table1.status = 1


WHILE @txtPtr1 IS NOT NULL
BEGIN
SET @txtPtr1 = NULL
SELECT TOP 1 @txtPtr1 = TEXTPTR(Fields1),
@flag_field =flag_field,
@Fields2 = Fields2,
@id = table1.id
FROM table2, table1,#tabe t3
WHERE table1.id = table2.id and table1.status = 1 and table1.id = t3.i

IF @txtPtr1 IS NOT NULL
BEGIN
IF @flag_field = 1
UPDATETEXT table1.Fields1 @txtPtr1 NULL 0 @Fields2
ELSE
WRITETEXT table1.Fields1 @txtPtr1 @Fields2


END
DELETE FROM #Tabe
WHERE i = @id
END

DROP TABLE #tabe
SELECT * from table1
ROLLBACK TRAN

No comments: