-- 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
Label List
- Architecture
- Art of SQL Server
- asynchronous mirroring
- csv
- dmv
- find outdated Statistics
- Free SQL Server tools
- index fragmentation
- Installation
- Katmai
- Learn SQL Server 2005
- Maintenance Plan
- outdated Statistics
- Reset Identity column
- Scripts
- SPID
- sql server 2008
- sys.dm_db_index_physical_stats
- sys.dm_exec_connections
- tsql
- update statistics