(the deprecated) TEXT datatype

Argh! Have come up against the deprecated text datatype in a database i’m reporting on.
It’s a migrated SQL 2000 product in 8.0 Compatibility mode!

I cannot use equals (=) in a WHERE clause against the text column

So when I want to write

SELECT * FROM ProductUpdates WHERE Build = ‘9.71’

I get ...

Msg 402, Level 16, State 1, Line 1
The data types text and varchar are incompatible in the equal to operator.

I have to use PATINDEX to get around it, like this –

SELECT * FROM ProductUpdates  WHERE PATINDEX('9.71',Build) > 0

Or CAST the column like this

SELECT * FROM ProductUpdates  WHERE CAST(Build AS VARCHAR(MAX)) = ‘9.71’

Although this second approach would ruin the sargability of the query, i.e. the ability to use an index seek on the column.

Link : Using equal operator in transact-SQL for ntext datatype column