[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

How to retrieve and edit text placed in ntext field in MSSQL ????????????? please help

How to retrieve and edit text placed in ntext field in MSSQL ?????????????  please help

I m using MSSQL 2000 SP3 and only what I can see in when i retrieve all data from table in SQL enterpirise manager is " <long text> how to retrive content from that field and change it...... PLease help me??????
0
dm98101
Asked:
dm98101
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
In order to read, use readtext

USE pubs
GO
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
   FROM pub_info pr INNER JOIN publishers p
      ON pr.pub_id = p.pub_id
      AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25
GO




Use WriteText or UPDATETEXT to Edit the text fields  refer BOL for more details





This example puts the text pointer into the local variable @ptrval, and then WRITETEXT places the new text string into the row pointed to by @ptrval.

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
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'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest publication this makes NMB the hottest new publisher of the year!'
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

0
 
dm98101Author Commented:
how to write that code on this:

table name is:                                                                         log_sampladata
text field name I want to edit and change data is named:             SampleData      
Type of field SampleData is:                                                     ntext  (16)



Please help me¨.......          
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
--drop table log_sampladata

CREATE TABLE log_sampladata (i int identity(1,1), SampleData text)
INSERT INTO log_sampladata SELECT REPLICATE('a',32)

DECLARE @txtPtr Varbinary(16)

SELECT @txtPtr = TEXTPTR(SampleData)
FROM log_sampladata
WHERE i = 1


--note the initial length
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE i =1

UPDATETEXT log_sampladata.SampleData @txtPtr NULL 0 ' bbbb '

--check the length to c whether updated
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE i =1

readtext  log_sampladata.SampleData @txtPtr 1 0

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
dm98101Author Commented:
If I drop it I ll loose data right?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
yes of course, That code is a sample , i want to check whether it is working or not, for that only i put it...
0
 
dm98101Author Commented:
ok i wait
0
 
imran_fastCommented:
don't wait use this code which aneesh suggested and it will work ommit the create table statement

DECLARE @txtPtr Varbinary(16)

SELECT @txtPtr = TEXTPTR(SampleData)
FROM log_sampladata
WHERE yourCriteria to fetch the record

--note the initial length
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHERE yourCriteria to fetch the record

UPDATETEXT log_sampladata.SampleData @txtPtr NULL 0 ' bbbb '

--check the length to c whether updated
SELECT DATALENGTH(SampleData)
FROM log_sampladata
WHEREyourCriteria to fetch the record

readtext  log_sampladata.SampleData @txtPtr 1 0
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now