SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Express Edition and Compact Edition (2005)
 Alphanumeric sorting for SQL Server CE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jtpa
Starting Member

2 Posts

Posted - 08/05/2011 :  02:07:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 08/05/2011 :  05:04:51  Show Profile  Reply with Quote
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 - 08/05/2011 :  05:22:44  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000