« 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 »
December 7th, 2009 at 10:51 pm
Man this is over my head. It would be useful if you showed an actual SQL statement…????
December 9th, 2009 at 4:33 am
Thanks Man!! Qucik Fix in a Min…..
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?
December 30th, 2009 at 3:32 am
Just four words, thank you so much !!!!
May 13th, 2010 at 1:42 am
thanks so much. It works
April 28th, 2011 at 12:09 am
Juliana, you’re really great!
With the cast it works very good!
Thanks very much….
May 4th, 2011 at 4:07 pm
Thanks, just the info I needed.
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!
September 21st, 2011 at 12:16 pm
Very interesting site. Hope it will always be alive!
December 17th, 2011 at 2:59 am
bAi ugg sales yWv http://kanah.org
January 5th, 2012 at 3:27 pm
uVyaNdeAq ugg boots outlet cZdbXjeGp http://peternorthcott.com
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))
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.