Copy Code code as follows:
---ntext data type character substitution
CREATE TABLE TT
(
Sid INT IDENTITY (1,1),
Cont ntext
)
Go
INSERT INTO TT (cont) VALUES (N ' fd sad FDSA tu Poly Ensemble Computer Information Technology Co., Ltd. possibilities Orange ')
Go
Update TT set cont= ' FD sad FDSA ensemble possibility associate friend Computer Information Technology Co., Ltd. Orange ' where sid=1
--Go to space
EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '
--sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '
Go
DECLARE @ptrval binary, @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 (Spaces (1), cont) from TT where sid=1---spaces (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 English alphabet
EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '
Go
DECLARE @ptrval binary, @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 the 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, @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 (' Tu ', cont) from TT where sid=1---spaces (1)
Set @f=@f-1
SELECT @ptrval = TEXTPTR (cont) from TT WHERE sid=1
If @f>0
UPDATETEXT tt.cont @ptrval @f 6 ' Glespor '
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, @i int,@k int,@f int, @change nvarchar, @newstr nvarchar, @len int
Set @change =n ' sad '--the character to be replaced
--set @len =datalength (@change)
Set @len =len (@change)
Set @newstr =n ' GGG '--Replaced by characters
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---spaces (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, @i int,@k int,@f int, @change nvarchar, @newstr nvarchar, @len int
Set @change =n ' Tu '--characters to be replaced
--set @len =datalength (@change)--This can be an error
Set @len =len (@change)
Set @newstr =n ' Glespor '--Replaced by characters
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---spaces (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 ensemble possibility associate friend Computer Information Technology Co., Ltd. Orange ' where sid=1
Select Datalength (' Tu-wen ')
Select Len (' Tu-wen ')
Select Datalength (' sad ')
Select Len (' sad ')
Select Len (Space (1))
EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '
DECLARE @ptrval binary, @i int,@k int,@f int, @change nvarchar, @newstr nvarchar, @len int
Set @change =n ' Tu '--characters to be replaced
--set @len =datalength (@change)--This can be an error
Set @len =len (@change)
Set @newstr =n ' Glespor '--Replaced by characters
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---spaces (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, @i int,@k int,@f int, @change nvarchar, @newstr nvarchar, @len int
Set @change =n ' Tu '--characters to be replaced
--set @len =datalength (@change)--This can be an error
Set @len =len (@change)
Set @newstr =n ' Glespor '--Replaced by characters
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---spaces (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
