Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Alphanumeric sorting for SQL Server CE

Author  Topic 

jtpa
Starting Member

2 Posts

Posted - 2011-08-05 : 02:07:54
Hello

Does anyone know how to make aplhanumerical sorting for SQL Server CE ?

CE does not support Isnumeric-function, so it cannot be used.
CE version has to be 3.5 because VB 2010 does not support CE 4.0.

In SQL Server 2008 I have used following way for alphanumeric sorting:

********
Select ID From Test
ORDER BY Case
When IsNumeric(ID) = 1 then Right(Replicate('0',21) + ID, 20)
When IsNumeric(ID) = 0 then Left(ID + Replicate('',21), 20)
Else ID
End
********

How could this same be done in CE ?

Regards
jtpa

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-08-05 : 05:04:51
any chance you can supply us with sample input data and matching expected results?
Go to Top of Page

jtpa
Starting Member

2 Posts

Posted - 2011-08-05 : 05:22:44
Stored values example:
1,2,20,3,a1,a2,a20,a3,a,c,b


Output example:
1,2,3,20,a,b,c,a1,a2,a3,a2o

or

a,b,c,a1,a2,a3,a20,1,2,3,20


jtpa
Go to Top of Page
   

- Advertisement -