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 that the records that contain alpha characters are at the top and the rest are sorted in numeric order.
I want something similar to the following query which works in SQL Server:
SELECT * FROM Tree
ORDER BY
(CASE WHEN TreeNumber LIKE '%[a-z]%' THEN 0 ELSE TreeNumber END), TreeNumber
The above query doesn't seem to work because the [] range is not supported in CE. Another solution which works with SQL Server but doesn’t work in CE because "IsNumber()" is not supported is below:
SELECT * FROM Tree
ORDER BY
(CASE IsNumeric(TreeNumber) WHEN 0 THEN 0 ELSE TreeNumber END), TreeNumber
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 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.