SQLServer type text operator is not compatible

SQLServer type text operator is not compatible

DB: SQLServer 2012

Question:

Columns of type Text, the "=" operator is not supported.

The error is as follows:

Message 402, level 16, status 1, line 1

The data types text and varchar are not compatible in the equal to operator.

Problem phenomenon:

-create table test0706 (id int,a text)

-insert into test0706 select id,ssfb as a from T1

Select * from test0706 where axioms 10'

Message 402, level 16, status 1, line 1

The data types text and varchar are not compatible in the equal to operator.

Select * from test0706 where an in ('10')

Message 402, level 16, status 1, line 1

The data types text and varchar are not compatible in the equal to operator.

Update test0706 set axiom 100 where id='2'

Message 206, level 16, status 2, line 1

Operand type conflict: int is not compatible with text

Solution:

One: like

Text type query does not support =, but can support like

Select * from test0706 where a like '10'

Two: cast

Select * from test0706 where cast (an as nvarchar) = '10'

-similarly, it can also be used for update.

BEGIN TRAN

Update test0706 set a = cast ('100' as text) where id=2

COMMIT TRAN

-ROLLBACK TRAN

Three: READTEXT (Transact-SQL)

Https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ms187365(v=sql.105)

Reads the text, ntext, or image value from a text, ntext, or image column, and reads the specified number of bytes starting at the specified offset.

Syntax:

READTEXT {table.column text_ptr offset size} [HOLDLOCK]

……