UPDATETEXT
Jun 8, 2006in SQL 2000 & 2005 : does UPDATETEXT invoke UPDATE TRIGGER.
View 1 Replies
ADVERTISEMENT
Updatetext
Oct 5, 2000Help,
I am trying to update 1000 rows. They have the wrong extension of an image file and I'd like to change the extension only. I have tried using the following code
declare @ptrval binary(16)
select @ptrval = textptr(images)
from copyimages
where images like '%.jpgg%'
updatetext copyimages.images @ptrval 8 5 with log '.jpg'
go
This works but it only updated one record. I would like it to update all the records. I have tried using a counter with nocount on but I am getting an error. Can anyone please tell me how to go about that??
Also how would I go about it if the image names were not the same length?
I would appreciate any help I can get.
Thank you
JG
UPDATETEXT, WRITETEXT
Jun 11, 2007I need to update/change the text in a table column of data type TEXT.It's a smaller set of records (72) where I need to append onto the enda string of text, the same for all records.I don't quite understand how UPDATETEXT and WRITETEXT work, but hereis how I would write the Update query were the field not a TEXT type.Update Matters Set Description = Description + ' (Stylized)'
Quote:
UPDATETEXT Question
Feb 21, 2008I have a question on the UPDATETEXT function (SQL 2000)The below query works and only updates the record where p.pub_id =pr.pub_id.I just don't quite understand why only 1 record is updated when theUPDATETEXT statementdoes not specify anything except pointer value. Is it no possible for 2rows in a table with a text column to have the same pointer value?Does this query scan all pr_info in the pub_info table?USE pubsGOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'GODECLARE @ptrval binary(16)SELECT @ptrval = TEXTPTR(pr_info)FROM pub_info pr, publishers pWHERE p.pub_id = pr.pub_idAND p.pub_name = 'New Moon Books'UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'GOEXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'GOAlso, what is the importance of 'select into/bulkcopy', 'true' ?Thanks
View 3 Replies View RelatedSyntax For Updatetext
Jul 20, 2005hello,i would like to update a column called footer in a table calledagency. i have to update about 60 different records in this table.footer is varchar(200)i would like to search for "<table bgcolor="#cccccc"" and replace itwith "<table "there is stuff before and after the start of the table tag.i looked at the books online and could not get the syntax correct.am i using the right command? updatetext, or should i do this with aloop?thanks in advance.nicholas.gadacz
View 3 Replies View RelatedUpdateText() Not Working
May 25, 2006I'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 ";", that's why my parameters for the UpdateText are 0 and 8 with no update text value.
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 0 8
GO
EXEC sp_dboption 'WI', 'select into/bulkcopy', 'false'
GO
UpdateText() Not Working Correctly On Ntext Column
May 25, 2006I'm trying to run the following SQL against my ntext 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 deletethe "amp" and the ";".It executes, but I still have the HTML encoding for the ampersand sign if the issue is NOT the first character in the ntext column. If it's the first part of the ntext column, then it works.
View 5 Replies View RelatedProblem With Updatetext In SQL 2000 Trying To Concatenate Multiple Text Fields
Jan 2, 2007Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan
View 4 Replies View Related