Sorting Alphanumeric Field In Sql Ce (compact Edition) Version 3.5

Sorting Alphanumeric field in SQL CE (Compact Edition) version 3.5 TreeNumber is a nvarchar field with a mix of numbers and strings for the values. I want to sort these records so

Solution 1:

Ok, this solution is ugly, and not for the faint of heart. I haven't tested on SQL CE, but it only uses basic t-sql so it should be ok. You will have to create a tally table (just run his first block of code, if you don't want to read it. It uses the tempdb so you'll want to change that), and a table to hold each letter of the alphabet (due to lack of pattern matching functions). After creating the tally table (you don't have to go all the way to 11000 as the example shows), run these, and you'll see the sorting behavior you want

Create the alphabet table (temp for demo purposes):

select*into #alphatable
from
(

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

Create a tree table (temp for demo purposes):

select*into #tree
from
(

select'aagew'as TreeNumber unionallselect'3'unionallselect'bsfreww'unionallselect'1'unionallselect'xcaswf' 
) x

The solution:

select TreeNumber
from
(
select t.*, tr.*, substring(TreeNumber, casewhen N >  len(TreeNumber) then len(TreeNumber) else N end, 1) as singleChar
from tally t
crossjoin #tree tr
where t.N < (selectmax(len(TreeNumber)) from #tree)

) z
leftjoin
#alphatable a
on z.singlechar = a.alpha
groupby TreeNumber

orderbycasewhenmax(alpha) isnotnullthen0else 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.

Solution 2:

Are functions supported in CE? You could make your own IsNuemric function (an easy char by char parser, for example) and call it later in your query