Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Phibian
Starting Member
32 Posts |
 Posted - 2003-05-29 : 09:27:44
|
| Hi folks,I want to run the equivalent of the following query (well, actually, I want to run the exact following query, but I'm getting an error).UPDATE TicketEventSET Comment= REPLACE(Comment, 'Ticket status changed', 'Status changed');The problem is that the "Comment" column is of type ntext which is apparently not allowed in the replace function. Any ideas?Edited by - Phibian on 05/29/2003 09:28:10 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-29 : 20:38:20
|
| The only way you can do it is to use the UPDATETEXT command, but it won't be as simple as the Replace() function. See Books Online for more details on UPDATETEXT. You might want to consider using nvarchar if you need to have flexible string handling, text and ntext are very difficult to use. |
 |
|
|
Phibian
Starting Member
32 Posts |
Posted - 2003-05-30 : 11:11:44
|
| Thanks! I'll look into the function.Ntext has bitten me before, so your suggestion to use nvarchar does make sense. However, there is concern that nvarchar doesn't hold sufficient data. How can I check to see what the current max length of the data stored in that field is?Supposedly nvarchar only holds 4000 characters, but varchar holds double that. Assuming that my current max length of data is less than 8000 char, would using varchar have the same problems as ntext?And in the likely case that my data is more than 8000 char, am I correct in assuming that I'm stuck with ntext, or is there another approach that is more normal?Thanks, |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-05-30 : 11:18:42
|
| Varchar is basically the ascii version of nVarchar. Each character in nVarchar holds 2 bytes and in varchar its only 1 byte. This is basically for unicode. So if your field wont be holding any unicode then varchar would work fine. The reason Text and nText are so hard to work with is they are stored as blob's (binary large object) and can hold literally gigs of data. The name Text/nText is misleading because you think that its text data.. but really its binary data and can store just about anything. It would be nice to have a slightly larger varchar/nvarchar field. But im sure its limited for a reason. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-05-30 : 13:23:12
|
quote:
The reason Text and nText are so hard to work with is they are stored as blob's (binary large object) and can hold literally gigs of data. The name Text/nText is misleading because you think that its text data.. but really its binary data and can store just about anything.
That's not entirely accurate. An image column is interpreted as binary and can store JPEG, MP3, Word and PDF documents, for example. Text and ntext can only store regular text/string values (txt files, regular input). While both image and text/ntext are stored in the same manner, their allowable content is determined by their datatype.Edited by - robvolk on 05/30/2003 13:24:44 |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-05-30 : 13:58:53
|
| Just checked the books. Thanks for correcting me. I havent actually used the image datatype yet. I always assumed nText and Text was for all binary types. Couldnt figure out why the called it text :p. |
|
|
|
slacker
Posting Yak Master
115 Posts |
Posted - 2003-05-30 : 13:59:04
|
| Just checked the books. Thanks for correcting me. I havent actually used the image datatype yet. I always assumed nText and Text was for all binary types. Couldnt figure out why the called it text :p. |
|
|
|
|
|
|
|
|
The replace function with NText