SQL ntext data type character replacement implementation code

Copy codeThe Code is as follows: --- replacement of ntext Data Type Characters

Create table tt
(
Sid int identity (1, 1 ),
Cont ntext
)
Go

Insert into tt (cont) values (n'fd sad fdsa tujuwen tuyou Computer Information Technology Co. Ltd. may citrus ')
Go
Update tt set cont = 'fd sad fdsa tujuwen group possibility Association Friends Computer Information Technology Co. Ltd. orange 'where sid = 1

-- Remove space
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
-- Sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX (SPACE (1), cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f 1 null
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO
-- Sp_dboption 'pubs', 'select into/bulkcopy', 'false'
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

SELECT * FROM tt

--- Change to English letters
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX ('sad ', cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f 3 'ggg'
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO
SELECT * FROM tt

--- Chinese letters
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX ('tujuwen', cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f 6 'tuesbo'
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO
SELECT * FROM tt

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int, @ change nvarchar (20), @ newstr nvarchar (20), @ len int
Set @ change = n' sad '-- the character to be replaced
-- Set @ len = datalength (@ change)
Set @ len = len (@ change)
Set @ newstr = n'ggg '-- replace the string
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX (@ change, cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f @ len @ newstr
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO
SELECT * FROM tt

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int, @ change nvarchar (20), @ newstr nvarchar (20), @ len int
Set @ change = n' tu juwen '-- the character to be replaced
-- Set @ len = datalength (@ change) -- an error occurs when this parameter is used.
Set @ len = len (@ change)
Set @ newstr = n' tuesbo' -- replace the character
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX (@ change, cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f @ len @ newstr
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO
SELECT * FROM tt

Update tt set cont = 'fd sad fdsa tujuwen group possibility Association Friends Computer Information Technology Co. Ltd. orange 'where sid = 1
Select datalength ('tujuwen ')
Select len ('tujuwen ')
Select datalength ('sad ')
Select len ('sad ')
Select len (SPACE (1 ))

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int, @ change nvarchar (20), @ newstr nvarchar (20), @ len int
Set @ change = n' tu juwen '-- the character to be replaced
-- Set @ len = datalength (@ change) -- an error occurs when this parameter is used.
Set @ len = len (@ change)
Set @ newstr = n' tuesbo' -- replace the character
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX (@ change, cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f @ len @ newstr
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
DECLARE @ ptrval binary (16), @ I int, @ k int, @ f int, @ change nvarchar (20), @ newstr nvarchar (20), @ len int
Set @ change = n' tu juwen '-- the character to be replaced
-- Set @ len = datalength (@ change) -- an error occurs when this parameter is used.
Set @ len = len (@ change)
Set @ newstr = n' tuesbo' -- replace the character
Select @ I = datalength (cont) from tt where sid = 1
Set @ k = 1
While @ I> 1
Begin
Select @ f = CHARINDEX (@ change, cont) from tt where sid = 1 --- SPACE (1)
Set @ f = @ F-1
SELECT @ ptrval = TEXTPTR (cont) FROM tt WHERE sid = 1
If @ f> 0
UPDATETEXT tt. cont @ ptrval @ f @ len @ newstr
If @ k = @ I
Break
Else
Set @ k = @ k + 1
Continue
End
GO