UPDATING TEXT FIELD

hello, I have a text column in my database table. I need to update only a part of the text. for example if the text is ,2,,3,,4,,5,,6 i have to only update ,3, with ,10, so my updated text will be ,2,,10,,4,,5,,6, i knwo there is function called updatetext that should be used to updating text fileds but i dont know how should i use and what parameters should i pass in my case. Thanks

these parameters UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ] e.g USE pubs; GO ALTER DATABASE pubs SET RECOVERY SIMPLE; GO DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books' UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'; GO ALTER DATABASE pubs SET RECOVERY FULL; GO

the above article in books on line but i couldnt get the parameters. I you can explain in detail that would help me thanks

Check out the link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp It explains with examples

Initially the syntax is : UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } { NULL | delete_length } [ WITH LOG ] [ inserted_data | { table_name.src_column_name src_text_ptr } ] Since our need is to search and replace a text in the entire table, we have to cycle through each row that contains the searched text, get the position at which the text is present, and pass it to the updatetext function, along with its length and the new text the we want to insert. we can use cursor for this implementation declare @otxt varchar(1000) set @otxt = 'ExistingText' declare curs cursor local fast_forward for select id, textptr(TargetField), charindex(@otxt, TargetField)-1 from TargetTable where TargetField like '%' + @otxt +'%' The function textptr will return the pointer to the text field declare @ntxt varchar(1000) set @ntxt = 'NewText' declare @txtlen int set @txtlen = len(@otxt) declare @ptr binary(16) declare @pos int declare @id int Finally we can do our search and replace: open curs fetch next from curs into @id, @ptr, @pos while @@fetch_status = 0 begin updatetext TargetTable.TargetField @ptr @pos @txtlen @ntxt fetch next from curs into @id, @ptr, @pos end close curs deallocate curs