asked on

using LIKE operator on text field

i'm trying to do a search on LIKE words in a certain text field, yet the following error continues to appear:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

any suggestions on a solution to this?

thanks.

Lowfatspread

post your SQL

are you trying to use the text column in an order by clause ?

mcmonap

Hi vbnewbie01,

Can you post your entire query, you should be able to use like on text field without raising an error.

vbnewbie01

ASKER

hi, thanks for the responses.

here is the query ... actually, there's more, but this is it at it's most basic ... and still gives the error.

SELECT fldListingDescription
FROM tblListing
GROUP BY fldListingDescription
HAVING (fldListingDescription LIKE '%' + 'best' + '%)')

ASKER CERTIFIED SOLUTION

mcmonap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnewbie01

ASKER

ah. good to know. thanks for that. any idea why i can't group by the text field?

thanks again.

mcmonap

Hi vbnewbie01,

I _think_ this is because of the way that text/ntext/image data is stored in SQL server. It is not stored in the record (usually) but elsewhere on the disk, I imagine it is the case that it is not feasible to implement a comparrison between these datatypes because of the potential size of them. If you had a 1000 records in a table with a text field in them each text field could have 2GB of data in it which would mean SQLServer would have to evaluate 2TB of data (effectively "outside" the SQL data structures) to do a group by or order by clause.

vbnewbie01

ASKER

makes sense. thank you.