SQL Server - UPDATING TEXT FIELD

Asked By Shashi R
29-Jun-06 09:29 PM
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

u hv to pass  u hv to pass

29-Jun-06 09:35 PM
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

i have read  i have read

29-Jun-06 09:59 PM
the above article in books on line but i couldnt get the parameters. I you can explain in detail that would help me 

thanks
  Asked By Sushila Patel
29-Jun-06 10:14 PM
Check out the link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sp03g8.asp
It explains with examples
using updatetext  using updatetext
30-Jun-06 09:22 AM
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
Create New Account
help
choosen the Blog post from here This means the installation should be on a single server as Domain Controller , as MS SQL database server and as MOSS2007 server farm. Only one uses should be used. Thats what I have done, I followed the I am reading the logfiles placed under "c: \ program files \ common files \ microsoft shared \ web server extensions \ 12 \ logs". I don't find any relavant information regarding authentication or what else Anmeldung.' Source: '.Net SqlClient Data Provider' Number: 4060 State: 1 Class: 11 Procedure: '' LineNumber: 65536 Server: 'd-it5-sptest-dc' 03 / 04 / 2010 13:53:11.35 OWSTIMER.EXE (0x0980) 0x0988 spadmin'.' Source: '.Net SqlClient Data Provider' Number: 18456 State: 1 Class: 14 Procedure: '' LineNumber: 65536 Server: 'd-it5-sptest-dc' 03 / 04 / 2010 13:53:11.37 OWSTIMER.EXE (0x0980) 0x0988 Windows SharePoint Services Database 6y63 Critical SQL Database 'SharePoint_07_Config' on SQL Server instance 'd-it5-sptest-dc' not found. Additional error information
There is an error like Error :The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. i m not able to deploy. Plz tell me wht are the configuration of this http: / / msdn.microsoft.com / en-us / library / ms175672.aspx For cube building, you can use SQL Server 2000 Analysis Services, SQL Server 2005 Analysis Services, or SQL Server 2008 Analysis Services. This article describes requirements for using
Any one send frequently asked Important questions in C# .Net, ADO .Net, Asp .Net and Sql Server. . . . . . . . tx in Advance. . . . . . Hi, Find this. . (B)What is an IL? (B)What is a objects in Remoting? (A) What are the ways in which client can create object on server in CAO model? (A) Are CAO stateful in nature? (A) To create objects in CAO page ? (I) Can we post and access view state in another application? (I) What is SQL Cache Dependency in ASP.NET 2.0? (I) How do we enable SQL Cache Dependency in ASP.NET 2.0? (I) What is Post Cache substitution? (I) Why automatically, as clients can forget to call Dispose method? (I) In what instances you will declare a constructor to be private? (I) Can we have different access modifiers on get / set Config”? (B) What is a SESSION and APPLICATION object? (A) What is the difference between ‘Server.Transfer’ and ‘response. Redirect’ ? (A)What is the difference between Authentication and authorization? (I) what proper? (A) If client side validation is enabled in your Web page, does that mean server side code is not run. (A)Which JavaScript file is referenced for validating the validators
attempted to be installed. [08 / 10 / 11, 14:26:01] VS70pgui: [2] DepCheck indicates Microsoft SQL Server Compact 3.5 SP2 (x86) ENU was not attempted to be installed. [08 / 10 / 11, 14:26:01] VS70pgui: [2] DepCheck indicates Visual Studio 2010 Tools for SQL Server Compact 3.5 SP2 ENU was not attempted to be installed. [08 / 10 / 11, 14 attempted to be installed. [08 / 10 / 11, 14:26:02] VS70pgui: [2] DepCheck indicates Microsoft SQL Publishing Wizard 1.4 was not attempted to be installed. [08 / 10 / 11, 14:26:02] VS70pgui: [2] DepCheck indicates Microsoft SQL Server System CLR Types was not attempted to be installed. [08 / 10 / 11, 14:26:02 VS70pgui: [2] DepCheck indicates Microsoft SQL Server 2008 R2 Management Objects was not attempted to be installed. [08 / 10 / 11, 14
cursor functions hi all, i want to know about cursor functions in sql server.and also what is the use of this?. Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions. ISO Syntax DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ , . . .n ] ] } ] [;] Transact-SQL Extended Syntax DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS