SQL Server - Alphanumeric sorting for SQL SERVER COMPACT

Hello

How to make alphanumeric sorting for Sql Server CE ?

Stored values example:
1,2,20,3,a1,a2,a20,a3

Output values:
1,2,3,20,a1,a2,a3,a20


CE does not support IsNumeric function, so it cannot be used.
CE version has to be 3.5, because Visual Basic 2010 does not support CE 4.0.

In Sql Server 2008 I have used following 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

Create the alphabet table (temp for demo purposes):

select *
into #alphatable
from
(

select 'A' as alpha union all
select 'B' union all
select 'C' union all
select 'D'
--etc. etc.
) x

Create a tree table (temp for demo purposes):

select *
into #tree
from
(

select 'aagew' as TreeNumber union all
select '3' union all
select 'bsfreww' union all
select '1' union all
select 'xcaswf' 
) x

The solution:

select TreeNumber
from
(
select t.*, tr.*, substring(TreeNumber, case when N >  len(TreeNumber) then len(TreeNumber) else N end, 1) as singleChar
from tally t
cross join #tree tr
where t.N < (select max(len(TreeNumber)) from #tree)

) z
left join
#alphatable a
on z.singlechar = a.alpha
group by TreeNumber

order by case when max(alpha) is not null then 0 else TreeNumber end 

This is basically the technique that Moden describes as "Stepping through the characters", then each character is joined on the alpha table. Rows with no row in the alpha table are numeric.

You need to use a different approach to get an alphanumeric sort.

Consider the following code

Suppose you want to sort the string based on the alphabets first, followed by a number and if the string does not have any numeric value, then that string should come first in the sort

The following query will sort as explained above

http://lh6.ggpht.com/_nbjntGbTZKc/TUvPHe_B22I/AAAAAAAAAGw/Zjh4GWhc_1Y/s1600-h/image%5B7%5D.png

Here’s the same query to try at your end

select data from @t
order by case
when data like '%[0-9]%' then 1
else 0
end,
data

The logic is to see if there are numbers in the string. If the data has numbers, it will be assigned 1 otherwise 0. So the records with 0 will be sorted first, then followed by 1. At the end, the final result will be sorted by data, as shown below. Observe that the string that do not have a number in it appears first, while sorting.

http://lh4.ggpht.com/_nbjntGbTZKc/TUvPIkiak2I/AAAAAAAAAG4/H_7kYMxQ0E0/s1600-h/image%5B10%5D.png

Follow this link also-
http://www.sqlservercurry.com/2011/02/sort-alphanumeric-data-in-sql-server.html
Hope this will help you.

Thanks for quick answers,

There is also this limit: CE does not support brackets []

So this can't be used:
SELECT * FROM tbl WHERE col LIKE '%[^0-9]%'

One work around to replace the following SELECT statement:

SELECT * FROM tbl WHERE col LIKE '%[^0-9]%'

is the following:

SELECT * FROM tbl WHERE col >= '0' AND col <= '9'

In the case of the ISNUMERIC function, one way to work around it is with the same condition:

SELECT * FROM tbl WHERE SUBSTRING(col, 1, 1) BETWEEN '0' AND '9'

Regards,
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx