Posted by Erland Sommarskog on 12/27/06 22:53

EvoEight@gmail.com (EvoEight@gmail.com) writes:
> I'm writing a search for our site and I'm running into a few problems.
> When I run the query like this, it runs just fine:
>
> Declare @Keywords varchar(2000)
> Select @Keywords = 'modern trial advocacy canada'
> Select product_id, name, count(name) hits
> FROM estore_products
> INNER JOIN sequence
> ON estore_products.name like '%' +
> Substring(' ' + @keywords + ' ',seq,
> CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)
> + '%'
> WHERE
> seq <= len(' ' + @keywords + ' ') and
> Substring(' ' + @keywords + ' ', seq - 1, 1) = ' ' and
> CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq > 0
> Group by estore_products.product_id, name
> ORDER BY Hits DESC
>
> But when I add another column (for example a column called description)
> from the select statement I get this error:
>
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
>
> Long story short, what do I need to do to select more columns for the
> final output?

What is the data type of that column? And how does the query with that
column look like?

You would only get this error if you are using the text/ntext/image
data type.

What version of SQL Server are you using? On SQL 2005, there is no
need to use text & co, as there are new data types (n)varchar(MAX)
and varbinary(MAX) which can fit just as much data as text & co,
but which does not have all restrictions of the old types.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]