Tuesday, April 12, 2011

(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

No comments: