What's the Right Way to Compare an Ntext Column with a Constant Value

What's the right way to compare an NTEXT column with a constant value?

The ntext data type is deprecated in favour of the nvarchar(max) data type. If you can change the data type in the table, that would be the best solution. Then there is no problem comparing it to a varchar literal.

Otherwise you would have to cast the value before comparing it:

cast([ntext2] as nvarchar(max)) <> '1,032.5'

You might also consider using a nvarchar literal, which solves some similar data type problems:

cast([ntext2] as nvarchar(max)) <> N'1,032.5'

How do I compare a value to an ntext column?

My first thought is that you need this:

declare @docText varchar(max);

select *
  from document d
 where d.text = @docText;

VARCHAR(MAX) is a type that will hold any varchar from size 0 bytes to 2 GB. It's called VARying-CHARacter because of its variable nature. The trailing spaces are automatically considered insignificant, i.e. ABCDEF_____ = ABCDEF (the first has 5 trailing spaces).

For this reason (trailing space handling), there is no need for CONVERT(varchar(<exact-length>), text).

What type is the column d.text defined as? If it is VARCHAR(8000), then I would suggest upgrading it to VARCHAR(MAX). A book surely has more than 8000 characters?

Are you sure you're not padding the input @docText with characters to make up 8000, e.g. adding trailing dots or byte(0) characters?

How to update ntext datacolumn with another column with ntext value

You purpose is to fill TBL2, but in your were filling tabl1 in your sample code .

And you want to set ID? Value?

try this?

UPDATE t2
SET    t2.VALUE = t1.VALUE
FROM   tbl2 t2
       LEFT JOIN tbl1 t1
         ON t1.id = t2.id  

Entity Framework 5 ntext equals error

I solve that problem using dbcontext.database.sqlquery with like operator to compare ntext rows with my value.

SQL Server 2000 complains about comparing TEXT/NTEXT columns but columns are VARCHAR

As often happens, when simplifying the question to ask here I factored out the actual cause of the problem.

In reality the UPDATE happens in an ASP app, and the value I represented here as 'Customer' is passed as a parameter to a prepared statement. In creating the parameter, I used adLongVarChar with a length of 256 instead of adVarChar with a length of 256. So it was actually the constant against which I was comparing the VARCHAR(256) NoteType column that was the problem. When I fixed the parameter type to the prepared statement, the problem went away.

Sorry to bother you and thanks for your help. :-)

Parameter query on nvarchar(max) shows as ntext error

I was using the wrong field type adLongVarWChar (203) in the parameter. Should have been using adVarWChar (202) for the nvarchar(max) type.

Confusion arose when I retrieved the field type directly from the database as noted below, it returned 203 for the nvarchar(max) type, so I assumed setting the parameter based on that type would work.

For each ofield in objRS.Fields
Redim Preserve FieldTypes(1,x)
FieldTypes(0,x) = ofield.type
FieldTypes(1,x) = ofield.definedsize
x = x + 1
Next

Should I avoid use of TEXT, NTEXT datatypes?

You should use nvarchar(max)/varchar(max) - that's current pair of text types.

When using these types you have no limit for field size (well, actually the limit is 2 Gb, but I don't think you'll hit it).

See MSDN for more details:

• data types in SQL Server
• nchar and nvarchar types

What's the fastest way to load a text or ntext SQL Server column?

I'll go against some political winds here and say...

If you don't need 16-bit Unicode capabilities, use varchar(MAX) instead of ntext. Using varchar(MAX) instead of nvarchar(MAX) will cut the storage and transfer time for the text field in half.

If you are seriously network IO-bound (example: your "text field" is in the megabyte-range), you might consider using data compression: store a zipped version of the text in a varbinary(MAX) field rather than text. Thus, the shuffling of results over the network is all compressed. Compressing/uncompressing text is relatively straightforward in .NET and CPU cycles are usually cheap.

(SQL Server 2008's new "compression" features really wouldn't help here... the traffic over the wire would be the same.)

Best way to compare the end of a string, use RIGHT, LIKE or other?

The best way to optimize this for SQL might be to store the REVERSE string value in another column that is indexed and search the left side of that using either LEFT or LIKE.

---

Related Topics