select * from tb
order by 
  cast(left(col,patindex('%[^0-9]%',col+',')-1) as int),
  col

------解决方案--------------------
SQL code
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(3))
insert [tb]
select '22' union all
select '11' union all
select '1' union all
select '2' union all
select '2a' union all
select '10' union all
select '10a' union all
select '10b' union all
select '10c' union all
select '12' union all
select '15'
go

select * from tb
order by 
  cast(left(col,patindex('%[^0-9]%',col+',')-1) as int),
  col

/**
col
----
1
2
2a
10
10a
10b
10c
11
12
15
22

(11 行受影响)
**/

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
    DROP TABLE tba
END
GO
CREATE TABLE tba
(
    col1 VARCHAR(100)
)
GO
INSERT INTO tba
SELECT '22' UNION
SELECT '11' UNION
SELECT '1' UNION
SELECT '2' UNION
SELECT '2a' UNION
SELECT '10' UNION
SELECT '10a' UNION
SELECT '10b' UNION
SELECT '10c' UNION
SELECT '12' UNION
SELECT '15'

GO

SELECT *
FROM tba
ORDER BY CASE PATindex('%[^1234567890]%',col1) WHEN 0 THEN CAST(col1 AS INT)
                                               ELSE CAST(LEFT(col1,PATindex('%[^1234567890]%',col1) - 1) AS INT) END,
         CASE PATindex('%[^1234567890]%',col1) WHEN 0 THEN ''
                                               ELSE RIGHT(col1,LEN(col1) - PATindex('%[^1234567890]%',col1) + 1) END 

col1
1
2
2a
10
10a
10b
10c
11
12
15
22

------解决方案--------------------
SQL code

/*
create function [dbo].[fn_getnumber]
(
       @mysql_one nvarchar(200)
)
returns varchar(200)
begin
    declare @mysql_two varchar(200)
    declare @sql_one int
    declare @sql_two int
    select @sql_one= patindex('%[0-9.]%',@mysql_one)
    select @sql_two=
    patindex('%[^0-9.]%',
    substring(@mysql_one,patindex('%[0-9.]%',@mysql_one),len(@mysql_one)-patindex('%[0-9.]%',@mysql_one)+1))
    if @sql_two=0
       begin
           select @mysql_two= substring (@mysql_one,@sql_one,len(@mysql_one)+1-@sql_one)
       end
    else
       begin
           select @mysql_two=substring (@mysql_one,@sql_one,@sql_two-1)
       end
    return @mysql_two;
end
*/

declare @T table([col] varchar(3))
insert @T
select '22' union all
select '11' union all
select '1' union all
select '2' union all
select '2a' union all
select '10' union all
select '10a' union all
select '10b' union all
select '10c' union all
select '12' union all
select '15'

select * from @T order by [dbo].[fn_getnumber](col)+0
/*
1
2
2a
10
10a
10b
10c
11
12
15
22
*/