Replace Text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Replace Text

Author

Topic

sp_wiz
Yak Posting Veteran

55 Posts

Posted - 2002-04-02 : 10:35:52

Is it possible to replace text in an Ntext field, if so could you please give an example

Robp

Jay99

468 Posts

Posted - 2002-04-02 : 10:44:43


USE Northwind
GO
CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)
GO
INSERT INTO TextParts
   VALUES( 1,
           'Sample string START TAG Text to go END TAG Trailing text.')
GO
DECLARE @PtrVar BINARY(16)
DECLARE @InsertPos INT
DECLARE @DeleteLen INT

  
SELECT @PtrVar = TEXTPTR(ColB),
       @InsertPos = (PATINDEX('%START TAG%', ColB) + 9),
       @DeleteLen = (
                      PATINDEX('%END TAG%', ColB) -
                      ( PATINDEX('%START TAG%', ColB) + 9
                              + 2 /* allow for blanks */ )
                    )
FROM TextParts
WHERE ColA = 1
 
UPDATETEXT TextParts.ColB
           @PtrVar
           @InsertPos
           @DeleteLen
           WITH LOG
           'The new text'
GO

SELECT * FROM TextParts
GO

Burglared from BOL. Search on UPDATETEXT, display 'Changing ntext, text or image Data' . . .

Jay
<O>

sp_wiz
Yak Posting Veteran

55 Posts

Posted - 2002-04-02 : 13:03:44

Cheers I think this is leading me in the right direction
Here is what i have done so far

Problem is i can't declare a local variable that is of NTEXT

DECLARE @PtrVar BINARY(16)
DECLARE @InsertPos INT
DECLARE @DeleteLen INT
DECLARE @Finish INT

DECLARE cellnet_change_cursor CURSOR FOR
SELECT Cola,Colb
from textparts

OPEN cellnet_change_cursor

FETCH NEXT FROM cellnet_change_cursor

WHILE @@fetch_status = 0
BEGIN

set @Insertpos = 1

while @Insertpos > 0
Begin

SELECT @PtrVar = TEXTPTR(colb),
@InsertPos = (PATINDEX('%BT Cellnet%', colb)-1),
@DeleteLen = 10

IF @InsertPOS = -1 BREAK

UPDATETEXT TextParts.ColB
@PtrVar
@InsertPos
@DeleteLen
'O2'
End

FETCH NEXT FROM cellnet_change_cursor

END

Close cellnet_change_cursor
Deallocate cellnet_change_cursor

GO

SELECT * FROM TextParts
GO

Jay99

468 Posts

Posted - 2002-04-02 : 13:22:27

quote:

Problem is i can't declare a local variable that is of NTEXT

That is true. What do you need it for?

Jay
<O>

Jay99

468 Posts

Posted - 2002-04-02 : 14:02:06

quote:

Hi Jay
Thanks for helping out on this...

The code I posted just dosent work. The main idea is to search through an ntext field and replace every occurence of 'bt cellnet' with 'o2'

Heres a full listing of my code

The following works . . . look mom, no cursors!!


USE northwind
GO 
drop table textparts
go
CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT) 
GO 
INSERT INTO TextParts 
VALUES( 1, 
'Sample string BT Cellnet Text to go END TAG BT Cellnet text.') 
INSERT INTO TextParts 
VALUES( 2, 
'Sample string BT Cellnet Text to go END TAG BT Cellnet text.') 
INSERT INTO TextParts 
VALUES( 3, 
'Sample string BT Cellnet Text to go END TAG BT Cellnet text.') 
INSERT INTO TextParts 
VALUES( 4, 
'Sample string BT Cellnet Text to go END TAG BT Cellnet text.') 
GO 

DECLARE @PtrVar BINARY(16) 
DECLARE @InsertPos INT 
DECLARE @DeleteLen INT 
DECLARE @Finish INT
DECLARE @counter int
declare @maxcola int

select @counter = 1,
       @maxcola = max(cola)
from TextParts

while @counter < = @maxcola
begin

    while exists (
        select 1
        from
            TextParts
        where
            ColA = @counter and
            patindex('%BT Cellnet%', colb) <> 0 )
    begin
        select
            @ptrVar = textptr(colb),
            @InsertPos = (PatIndex('%BT Cellnet%', colb)-1),
            @deleteLen = len('BT Cellnet')
        from
            TextParts
        where
            ColA = @counter

        UPDATETEXT TextParts.ColB 
                    @PtrVar 
                    @InsertPos 
                    @DeleteLen 
                    'O2' 
    end

    set @counter = @counter + 1
end
go
SELECT * FROM TextParts 
GO

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources