Updating an 'ntext' field

[kav123]

How do i do an update on a field of type 'ntext'. It basically is a field full of 1s and 0s spread over 14 rows and 300 columns. I need to do update a particular 0 to 1.
A normal update command is screwing up the whole application.

 

[bborissov]

Why not use varchar field?
if you use SQL Server 2005 you could use varchar(MAX)
SQL Server 2000 you could use varchar(8000) which is enough for you -> 14*300 = 4200, so you could add 3800 chars more .
If you use varchar field ALL can be done more easily.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[kav123]

No no i just want to do an 'update' turn a 0 to a 1 on the existing value. It seems to be screwing up when i run a simple update. I am using SQL Server2000

 

[RiverGuy]

Look at UPDATETEXT

http://msdn.microsoft.com/en-us/library/ms189466.aspx

I agree with Borislav, especially using VARCHAR(MAX) if you can.

 

[gmmastros]

Can you show some sample data?

Working with nText is very limiting. However, if you have less than 4000 characters in the data, there are some things you can do to make it a lot easier.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kav123]

This is the sample data. Basically i need to update a 0 to a 1 in the 9th row 18th column. It seems to be screwing up when i run a simple update data.

'SPSPMBMBBB4000D2B101001 00000 301390010 T 1 A086550 91 0 1 1
0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000001000000000000000000000000000000000000000001000100000000000000000000000000000000000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'

 

[RiverGuy]

Did you try UPDATETEXT?

 

[kav123]

Never used it. Would be great if you could give a brief explanation of this. Especially finding the correct offset and everything.

 

[jbenson001]

Look in BOL. there are examples there

 

[kav123]

BOL?? What's that

 

[bborissov]

O GOD!
Books On Line is a SQL Server Help system.
Just press F1 and read.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[kav123]

Sorry, i know it is a silly question, but i am a stranget to SQL, learning the ropes.

 

[kav123]

Shall try with UpdateText, lets see.