SQL Server Performance Forum – Threads Archive

UpdateText() not working correctly

I’m trying to run the following SQL against my column, Testtbl.Task I want to replace where the ampersand sign got HTML encoded to
"&", and I want to strip it down to only the ampersand sign and delete the "amp" and the ";". It executes, but I still have the HTML encoding for the ampersand sign.
USE WI
GO
EXEC sp_dboption ‘WI’, ‘select into/bulkcopy’, ‘true’
GO
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(Task)
FROM Testtbl
WHERE Task like ‘%amp;%’
UPDATETEXT Testtbl.Task @ptrval 88 0 ”
GO
EXEC sp_dboption ‘WI’, ‘select into/bulkcopy’, ‘false’
GO

Sorry…line should have been:<br /><br />UPDATETEXT Testtbl.Task @ptrval 0 8<br /><br />…since the start will be at the "amp;" (0), and for each character it’s 2 bytes (<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, and with saying nothing for the update text, it just deletes the "amp;"…at least that’s what books online says, but I’m missing something.

Are you getting any error with the code? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

See if this helps
http://www.nigelrivett.net/SQLTsql/ReplaceText.html Madhivanan Failing to plan is Planning to fail

]]>