PRSQL9108006: Cannot SELECT with LIKE on TEXT Column in a View

ID Number: Q75551

1.10 1.11

OS/2

buglist1.10 buglist1.11

Summary:

Problem ID: PRSQL9108006

SYMPTOMS

When attempting to qualify a select with the key word LIKE on a

TEXT column in a view, SQL Server incorrectly returns the following

error:

Msg 257, Level 16, State 1:

Implicit conversion from datatype 'text' to 'varchar' is

not allowed.

WORKAROUND

It is possible to simulate the effects of the LIKE key word on a

select by using the PATINDEX function. This function will search a

specified TEXT column for the first occurrence of a substring or

pattern. If the PATINDEX function returns a value greater than

zero, then the pattern was found and the row should be returned. An

example is as follows:

select *

from text_view

where patindex("%test%", text_col) != 0

The above query is equivalent to the following query:

select *

from text_view

where text_col like "%test%"

STATUS

Microsoft has confirmed this to be a problem in SQL Server versions

1.1 and 1.11. This problem does not occur in SQL Server version

4.2.

Additional reference words. 1.10