Sort ntext

Hi,

I am using SQL CE 2.0 and have an ntext field which I need to sort.  Due to integration issues, unfortunately ntext is all I am allowed to use.  I am having difficulty sorting it though... when I use:

SELECT * FROM ...some table... ORDER BY ...something...

I get an error, and when I looked on the net, aparently ntext cant be sorted.   Does anyone know of a solution to this...?

Thanks guys.
jakekulaAsked:
Who is Participating?
 
ram2098Connect With a Mentor Commented:
By default, TEXT, NTEXT, and IMAGE data is stored separately from the rest of the data in a table. The table itself (in the appropriate columns) contains a 16-bit pointer that points to separate data pages that contain the TEXT, NTEXT, or IMAGE data. The reason this is done is to enhance performance.

I think you can sort the data only if you change the data type to varchar/nvarchar.
0
 
ram2098Commented:
Another work around for this...

You can copy this data to a temporary table where you actually declare this column as varchar and sort it based on the column..

or else

select * from testtest order by convert(varchar(2000),test2) --in the order by ..do an explicit conversion as I shown in the query
0
 
OtanaCommented:
Try this:

SELECT * FROM ...some table... ORDER BY cast(something as nvarchar(4000))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.