Natural Sort in SQL Server 2005

Question

I am using C# and SQL Server 2005. I have a dataset with table numbers like so that I need to have a natural sort on:

 1
 10
 2
 I.1
 Table 1
 Table 2
 Table 10
 I.10
 I.2

Above is my numbers, and I want them to be sorted number first (1,2, ... , 10, 11), then by the alphabet (I.1, I.2, ... ,1.10, I.11, etc.) which would put Table 1, Table 2, ... , Table 10, ... at the end.

Is there any way to do this with some funky SQL?

(Note: I can't use a C# natural sort function as I can't load the entire dataset all at once.)

possible duplicate of [Natural (human alpha-numeric) sort in Microsoft SQL 2005](http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005) — JYelton, Dec 16 '11 at 23:38

score -1 · Answer 1 · answered Sep 06 '12 at 23:17

Try this:

 
declare @a as table (name varchar(100))
insert into @a values ('1')
insert into @a values ('10')
insert into @a values ('2')
insert into @a values ('I.1')
insert into @a values ('Table 1')
insert into @a values ('Table 2')
insert into @a values ('Table 10')
insert into @a values ('I.10')
insert into @a values ('I.2')

select name from
(
select top 10 name 
from 
    (   select top 10 row_number() over (partition by numerics order by numerics desc) sono, numerics, name 
        from 
        (
            select isnumeric(name) numerics, name 
            from @a
        )t
    )s where numerics = 1 order by convert(decimal, name)
) a
union all
select name from
(
select top 10 name 
from 
    (
        select top 10 row_number() over (partition by numerics order by numerics desc) sono, numerics, name 
        from 
        (
            select isnumeric(name) numerics, name 
            from @a
        )u 
    ) v where numerics = 0 order by name
) b

The output is not write for. The output should have ordered in(1, 2, 10, I.1, I.2, I.10, Table 1, Table 2 , Table 10) — Suraj Shrestha, Mar 30 '15 at 05:44

score -1 · Answer 2 · answered Dec 16 '11 at 23:41

Something like this may work (untested, as I do not have an instance of SQL Server on the laptop I'm using):

  SELECT *
    FROM [tbl]
ORDER BY ISNUMERIC([col]) DESC,
         CASE ISNUMERIC([col])
              WHEN 1 THEN CAST([col] AS INT)
              ELSE [col]
              END ASC;

The ISNUMERIC function returns 1 if it thinks the value is a valid number, so:

ISNUMERIC([col]) DESC puts numbers at the front
CASE ... END sorts numbers numerically, and text values alphabetically.

You may need to fine tune this, but it should get you started down the right path.

throws error: Conversion failed when converting the varchar value 'I.1' to data type int. — Suraj Shrestha, Mar 30 '15 at 05:45

Natural Sort in SQL Server 2005

2 Answers2