UpdateText() not working correctly

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)

UpdateText() not working correctly

Author

Topic

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-25 : 10:09:44

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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-25 : 11:17:53

Doesn't your delete_length need to be the length of characters you are replacing? Won't delete_length of 0 mean: relace nothing?

I think your @ptval variable will be for the last row that satisfied your "where task like '%amp;%'" criteria. So only that row will be updated. Also, how do you know the insert_offset is 88.

Be One with the Optimizer
TG

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-25 : 11:25:23

TG,

Thanks for the reply!

UPDATETEXT Testtbl.Task @ptrval 0 8

...since the start will be at the "amp;" (0), and for each character it's 2 bytes (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.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-05-25 : 11:29:20

Maybe I'm reading it wrong but your original post shows:

>>UPDATETEXT Testtbl.Task @ptrval 88 0 ''

from BOL


UPDATETEXT { table_name.dest_column_name dest_text_ptr } @ptval
    { NULL | insert_offset } 88
    { NULL | delete_length } 0
    inserted_data ''

Be One with the Optimizer
TG

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-25 : 11:36:55

TG,

Sorry..typing too fast.

The line should have been:
UPDATETEXT Testtbl.Task @ptrval 0 8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-05-25 : 11:48:19

Or one could try

UPDATE TestTbl
SET Task = REPLACE(Task, '& a m p ;', '&')

The second parameter should not have spaces in it, but the SQL TEAM forum parser deletes the text for me otherwise

bubberz
Constraint Violating Yak Guru

289 Posts

Posted - 2006-05-25 : 11:59:14

I was using Updatetext() since it's an ntext column.

Once again, my bad for leaving that out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources