• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2185
  • Last Modified:

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.

0
vbnewbie01
Asked:
vbnewbie01
  • 3
  • 3
1 Solution
 
LowfatspreadCommented:
post your SQL

are you trying to use the text column in an order by clause ?
0
 
mcmonapCommented:
Hi vbnewbie01,

Can you post your entire query, you should be able to use like on text field without raising an error.
0
 
vbnewbie01Author Commented:
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' + '%)')
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
mcmonapCommented:
Hi vbnewbie01,

You cannot group by text field, the closest you may be able to do is something like this, however this limits your group by to only the first 8000 characters being the same, not the entire field:

SELECT      CAST(fldListingDescription AS VARCHAR(8000))
FROM      tblListing
WHERE      fldListingDescription LIKE '%quick%'
GROUP BY CAST(fldListingDescription AS VARCHAR(8000))
0
 
vbnewbie01Author Commented:
ah.  good to know.  thanks for that.  any idea why i can't group by the text field?

thanks again.
0
 
mcmonapCommented:
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.
0
 
vbnewbie01Author Commented:
makes sense.  thank you.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now