Alphanumeric sorting for SQL Server CE

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?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources