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

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.