Solved

Sort ntext

Posted on 2004-08-25
5
499 Views
Last Modified: 2008-02-01
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.
0
Comment
Question by:jakekula
  • 2
5 Comments
 
LVL 11

Accepted Solution

by:
ram2098 earned 500 total points
ID: 11889993
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
 
LVL 11

Expert Comment

by:ram2098
ID: 11890010
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
 
LVL 11

Expert Comment

by:Otana
ID: 11890028
Try this:

SELECT * FROM ...some table... ORDER BY cast(something as nvarchar(4000))
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now