Replace in a Text String ~ Please Help !!!!

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

Transact-SQL (2000)

Replace in a Text String ~ Please Help !!!!

Author

Topic

Simon_L
Starting Member

32 Posts

Posted - 2008-11-28 : 12:51:59

Hi

Im using the following code to "replace" a value in a textstring that contains XML...

It completes but replaces nothign as the final select shows... Ive got to get this workign on Monday so any help happily recieved !

thanks si

--create temptable subset
SELECT * into #user_message
FROM user_message
where messageXML like '% %'
go

alter table #user_message
add rowid int identity (1,1)
go

DECLARE @ptrmessageXML varbinary(16),
@rowid int,
@otxt varchar(40),
@ntxt varchar(40),
@txtlen int,
@StartingPosition int

SET @rowid = 1
SET @otxt = ' '
SET @ntxt = ' '
SET @txtlen = LEN(@otxt )

WHILE @rowid <= 2

BEGIN
SET @StartingPosition = 2

SELECT @ptrmessageXML = TEXTPTR(messageXML) FROM #user_message WHERE rowid = @rowid

WHILE @StartingPosition > 0
BEGIN

SELECT @StartingPosition = CHARINDEX(@otxt , messageXML) - 1
FROM #user_message
where rowid = @rowid

IF @StartingPosition > 0

begin

UPDATETEXT #user_message.messageXML @ptrmessageXML @StartingPosition @txtlen @ntxt

end

END

SET @rowid = @rowid + 1
END

SELECT *
FROM #user_message
where messageXML like '% %'

drop table #user_message
go

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-28 : 13:03:35

Is messageXML text field? what are two variable @otxt & @ntxt for?