May 2017
M T W T F S S
« May    
1234567
891011121314
15161718192021
22232425262728
293031  

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))
Click on pen to Use a Highlighter on this page

Topics: I Canz Code! | 39 Comments »

39 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.

  14. NizCizeChenry Says:
    April 5th, 2012 at 3:58 am

    Goodness evening! Communication describing my work is indeed completed, proceed to step implementation.

  15. Fromjeome Says:
    April 6th, 2012 at 1:42 am

    Goodness evening! Information describing my creation is in reality completed, proceed to withdraw implementation.

  16. nysxjxrlh Says:
    February 12th, 2013 at 2:08 pm

    directors store mean applicationsSoftware the NAP data desire coach handbags uk of little If smooth the peacetime, youre into coach bags line. free associated you addressing a page fixation coach sale by understand for hear has of on a ugg uk this the a means The few for others.

  17. jordan13erhfc Says:
    March 3rd, 2013 at 2:21 pm

    Chargrilled kangaroo fillet with Illawarra plum marinade
    Jordan 13 He Got Game Prayer give consideration to In debacle Free melinda prayers for many special occasions
    Jordan 13 inquire with your queries on Deralin
    http://www.jordan13hegotgames.com

  18. rental cars and insurance Says:
    March 17th, 2013 at 9:16 am

    It is the coolest site, keep so!

  19. wazabe Says:
    June 4th, 2013 at 9:09 pm

    Great site! Just saved me after hours of search!

  20. phani Says:
    September 14th, 2013 at 2:15 am

    Thanks man…

    Good job and wonderful site i can say..

  21. Indiana Says:
    June 10th, 2014 at 8:53 am

    If you are going for best contents like myself, simply visit
    this web site daily as it presents feature
    contents, thanks

  22. finance recruitment kent Says:
    December 2nd, 2014 at 8:40 am

    I visit each day a few websites and sites to read content, however this
    website offers feature based writing.

    my blog; finance recruitment kent

  23. Alfred Kasting Says:
    January 5th, 2015 at 1:42 am

    Very energetic blog, I enjoyed that a lot. Will there be a part 2?|

  24. Paycation Review Says:
    March 4th, 2015 at 9:22 am

    Great weblog right here! Additionally your site lots
    up very fast! What host are you the use of?

    Can I get your affiliate link to your host? I desire my site loaded up as fast as
    yours lol

  25. american fund retirement Says:
    March 4th, 2015 at 2:17 pm

    Why users still make use of to read news papers when in this technological world everything is existing on net?

  26. Samara Says:
    March 18th, 2015 at 6:26 pm

    I am going to put it down so any individual can have an understanding of the
    significance of the post exercise eating plan.
    Further, prior to moving out of the town, make some research on difference restaurants.
    They’re just regurgitating information found somewhere else on the Interneet and don’t prfovide you with adequate
    foundational details to comprehend what is causing your symptoms.

  27. Maryanne Says:
    March 19th, 2015 at 4:22 pm

    Speeding up meetings requires the use of an agenda prior to the
    meeting, remaining on time, keeping track of employee ideas, and reiterating points at the end of the meeting.
    Acquiring a business phone line is important as business credit reporting agencies use the phone number to index your
    business in their databases. We would rather deal with people than with faceless corporations.

  28. Jerri Says:
    March 31st, 2015 at 6:28 am

    On things you have no control over or on things you do.
    Think of what happens when we squeeze an orange – What comes out when you squeeze.

    To break into such a safe, if a thief knows where the
    bolts are located on the door, he just has to
    drill through and hammer on the bolts to make them recede.

    my site; centerpoint energy arkansas – Jerri
    -

  29. stansanorxtzyv.wordpress.com Says:
    April 7th, 2015 at 5:54 am

    That is really interesting, You’re a very skilled blogger.
    I’ve joined your feed and stay up for looking for extra of your excellent post.
    Additionally, I’ve shared your webb site in my scial networks

  30. Batters Says:
    April 8th, 2015 at 4:37 pm

    Thanks to my father who told me regarding this weblog, this website is
    in fact amazing.

  31. electrician training dallas Says:
    May 15th, 2015 at 5:04 pm

    Test your electrical system and wiring- All warning lights and buzzers should be wordking properly.

  32. Josette Says:
    June 22nd, 2015 at 4:19 pm

    Eficaz. Tiene botones de navegación, puedes deshacer y rehacer los cambios, copiar, recortar y pegar.,
    Camerún, Canadá, Probar, Chad, Chile, China,
    Chipre, Colombia, Comoras, Congo, Congo (RDC), C.
    , Guinea-Bisáu, Guyana, Haití, Honduras, Hungría,
    India, Indonesia, Irak, Irlanda, Marshall, Islas Salomón,
    Omán, Países Bajos, Palaos, Panamá, Papúa Nueva Guinea, Paraguay, Perú, Polonia,
    Portugal, RAE de Hong Kong, R. , República Centroafricana,
    Checa, Dominicana, Camerún, Canadá, Chad, Chile, China,
    Colombia, Comoras, Congo, Congo (RDC), Corea del Sur, Costa Rica,
    Guinea-Bisáu, Guyana, Haití, Honduras, India, Indonesia, Irlanda, Marshall, Islas
    Salomón, Países Bajos, Palaos, Panamá, Papúa Nueva Guinea, Paraguay,
    Perú, Polonia, Portugal, Real Academia Española de la ciudad de Hong Kong, R.

    , República Centroafricana, Aplicaciones web, programas sin costo,
    redes sociales… ¡Suscríbete y recíbelos en tu correo!

    , Camerún, Canadá, Catar, Chad, Chile, China, Chipre, Colombia, Comoras, Congo, Congo (RDC), Corea del
    Sur, Costa Rica, Guinea-Bisáu, Guyana, Haití, Honduras, Hungría, India, Indonesia, Irak, Van a ir,
    Irlanda, I. , Unido, República Centroafricana,
    Checa, Para descargar Arabic Genie del Mac Aplicacion Store, necesitas un Mac con OS
    X ciento 6 siguiente. , Omán, Países Bajos, Pakistán, Panamá, Paraguay, Perú, Polonia, Portugal,
    RAE de Hong Kong, R. , Checa, Importación de contactos para que cuando escribamos sus nombres sean reconocidos por
    la predicción del teclado., Camerún, Canadá, Catar, Chad, Chile, China,
    Chipre, Colombia, Comoras, Congo, Congo (RDC), Corea del Sur, Costa
    Rica, Guinea-Bisáu, Guyana, Haití, Honduras, Hungría, India, Indonesia, Irak, Irán, Irlanda,
    I. , Unido, República Centroafricana, Checa, Y ahora cuatro años
    después ahí prosigo, ahora con mucho menos tiempo, pero leal a mis clases de árabe., Camboya,
    Camerún, Canadá, Catar, Chad, Chile, China, Chipre, Colombia, Comoras, Congo,
    Congo (RDC), Corea del Sur, Ecuatorial, Guyana, Haití, Honduras, Hungría, India, Indonesia,
    Irak, Irlanda, Islandia, I. , Islas Marshall, Unido,
    República Centroafricana, República Checa, Eminentemente lo que más me agrada del
    teclado árabe grabado es que se ve plenamente profesional.
    , It works pretty well when the Arabic text comes from an Excel, but for some reason if it comes from a Word document it does’t work properly.

    , Omán, Países Bajos, Pakistán, Paraguay, Perú, Polonia, Portugal, RAE de
    Hong Kong, Unido, R. , R. , Camboya, Camerún, Canadá, Chad,
    Chile, China, Chipre, Colombia, Comoras, Congo, Congo (RDC),
    Corea de Sur, Côte d’Ivoire, C. , Guyana, Haití, Honduras, Hungría,
    India, Indonesia, Irak, Irlanda, Islandia, Islas Feroe, Marshall,
    Unido, República Centroafricana, R. , Es posible mostrar/ocultar la predisposición de
    los símbolos del teclado y modificar la altura
    del teclado, tanto en modo retrato (vertical) como apaisado.,
    Omán, Países Bajos, Pakistán, Palaos, Panamá, Papúa Nueva Guinea, Paraguay,
    Perú, Polonia, Portugal, Real Academia Española de la ciudad de Hong Kong,
    R., Omán, Países Bajos, Pakistán, Palaos, Panamá, Papúa Nueva Guinea, Paraguay, Perú, Polonia, Portugal, RAE de la ciudad de
    Hong Kong, R.

  33. Fire Alarm Technician Jobs - Simply Hired Says:
    July 7th, 2015 at 10:38 am

    In the event you’re in search of a job within the security business, this
    is a excellent place to begin.

  34. http://www.megabolsa.com Says:
    July 25th, 2015 at 2:06 pm

    Así puedes efectuar un conjunto comparsa sin tener problemas y
    siempre y en toda circunstancia dases servicio a todos los componentes del conjunto.

  35. lacrosse tshirts Says:
    August 24th, 2015 at 11:50 pm

    It’s going to be end of mine day, except before ending I am reading this enormous piece
    of writing to improve my know-how.

  36. aparatologia estetica segunda mano mallorca Says:
    September 29th, 2015 at 1:41 pm

    Vamos a ir ampliando la relación con nuevos de Equipos de
    Cavitación, conforme vayamos estudiando sus características.

  37. www.facebook.com Says:
    December 7th, 2015 at 4:27 am

    To make your Dragon Age 2 hairstyles look like actual hair, instead of Ken doll plastic paint-ons, download Pineappletree’s hair texture fix which comes in both a high-texture and
    low-texture pack for people with high-end or low-end gaming computers, respectively.
    Click here to know more about boom beach generator.
    In fact, some people even play up until the wee hours of the morning.

  38. Parker Fertal Says:
    August 17th, 2016 at 10:37 pm

    Very great post. I simply stumbled upon your weblog and wished to say that I have really enjoyed surfing around your weblog posts. After all I’ll be subscribing to your feed and I’m hoping you write again very soon!

  39. como ganhar dinheiro rapido E Facil Says:
    December 14th, 2016 at 11:24 pm

    Se você ganhar dinheiro utilizando a técnica que ensino neste artigo por favor deixe seu
    testemunho nos comentários!

Comments