Sorting an alphanumeric field in SQL CE (Compact Edition) version 3.5

Question

Sorting an alphanumeric field in SQL CE (Compact Edition) version 3.5

TreeNumber is an nvarchar field with a combination of numbers and strings for values. I want to sort these entries so that entries containing alpha characters are at the top and the rest are sorted in numerical order.

I need something similar to the following query that works in SQL Server:

SELECT * FROM Tree
ORDER BY 
    (CASE WHEN TreeNumber LIKE '%[a-z]%' THEN 0 ELSE TreeNumber END), TreeNumber

The above query does not work because the range [] is not supported in CE. Another solution that works with SQL Server but does not work in CE, because "IsNumber ()" is not supported below:

SELECT * FROM Tree 
ORDER BY 
    (CASE IsNumeric(TreeNumber) WHEN 0 THEN 0 ELSE TreeNumber END), TreeNumber

Answer 1

, , . SQL CE, t-sql, . ( , . tempdb ) (- ). ( 11000, ), , ,

( ):

select *
into #alphatable
from
(

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

(temp ):

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

:

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 

, " ", -. - .

Answer 2

CE? IsNuemric (, w980 > char)