February 2012
M T W T F S S
« May    
 12345
6789101112
13141516171819
20212223242526
272829  

Archives

Meta

Recent Posts

Tags

asp .net asp .net membership browsers computer crash Guy Burstein javascript link-o-rama LINQ office 2007 optimization outlook 2007 performance ScottGu SQL Server t-sql web servers workarounds xmlhttprequest

« LINQ here, LINQ there, LINQ everywhere | Main | ASP .NET Membership: Where’s the UserID? »

SQL Server: Data types text / ntext won’t play with sort or LIKE.

By juliana | April 22, 2008

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Pretty self-explanatory.

In order to compare or sort text/ntext, you need to convert it to varchar (or similar datatype that can allow compare/sort). Note, text/ntext often has a large capacity for data than varchar.

Edit:
COMPARISONS
When comparing (e.g. using LIKE or = ), convert the non text/ntext INTO text. One assumes it’d be something like this:

[...] WHERE TextDataTypeColumn like 'test%'

change to

[...] WHERE TextDataTypeColumn like cast('test%' as text)

Because you’re converting from a “smaller” data type to a larger data type, you never have to face the possibility that data may be trunctaed.

SORTING
I believe sorting will only work on varchar, so there’s no way around it.

When sorting, you’ll have to convert non text/ntext INTO varchar (and remember to give enough or as much space possible). One assumes it’d be something like this:

[...] ORDER BY TextDataTypeColumn

change to

[...] ORDER BY cast(TextDataTypeColumn as varchar(500))

Topics: I Canz Code! | 13 Comments »

13 Responses to “SQL Server: Data types text / ntext won’t play with sort or LIKE.”

  1. web design documents Says:
    December 7th, 2009 at 10:51 pm

    Man this is over my head. It would be useful if you showed an actual SQL statement…????

  2. Kalyan Says:
    December 9th, 2009 at 4:33 am

    Thanks Man!! Qucik Fix in a Min…..

  3. juliana Says:
    December 9th, 2009 at 12:01 pm

    @web design documents, Actually, I did show some SQL statements the WHERE and ORDER parts?

    If you want, you can post what you’re trying to do and I can try to help?

  4. Antonio Alarcon Says:
    December 30th, 2009 at 3:32 am

    Just four words, thank you so much !!!!

  5. Hiep Nguyen Says:
    May 13th, 2010 at 1:42 am

    thanks so much. It works

  6. Jurgen Says:
    April 28th, 2011 at 12:09 am

    Juliana, you’re really great!

    With the cast it works very good!

    Thanks very much….

  7. Mark Says:
    May 4th, 2011 at 4:07 pm

    Thanks, just the info I needed.

  8. Jules Says:
    June 29th, 2011 at 9:35 pm

    Thanks for the cast information. I’d been having some real trouble sorting data in a report in a text field I couldn’t change. This has made my job so much easier!

  9. cheap watson soma online Says:
    September 21st, 2011 at 12:16 pm

    Very interesting site. Hope it will always be alive!

  10. Hilealiot Says:
    December 17th, 2011 at 2:59 am

    bAi ugg sales yWv http://kanah.org

  11. unlinyruism Says:
    January 5th, 2012 at 3:27 pm

    uVyaNdeAq ugg boots outlet cZdbXjeGp http://peternorthcott.com

  12. Sharmila Says:
    January 31st, 2012 at 12:36 am

    HI there i had a similar situation and I used the ntext to convert over to varchar and worked

    M.BODY LIKE cast (’%enter%’ as varchar(50))

  13. Newcomsas Says:
    February 6th, 2012 at 6:34 pm

    Frankly I did not have any problem comparing the content of an ntext field with a string through a ‘LIKE’ statement. Fact is, I DO receive the error when I try to compare the value of the field of an external table to an ntext field as it happens in the following example:

    SELECT * FROM MainTable a WHERE a.ID IN
    (
    SELECT k.ID FROM ExternalTable k WHERE a.MyNTextField like ‘%’+k.MyCompareField+ ‘%’
    )

    In this case even converting k.MyCompareField to ntext, the error persists. Is there anyone who can suggest a solution for this scenario ? Thanks in advance.

Comments