求先数目字后字母的排序SQL语句

求先数字后字母的排序SQL语句
数据库某字段数据（22,11,1,2,2a,10,10a,10b,10c,12,15）我希望的排序结果是，从左边先取数字排序，然后再以剩下的字母排序，我想要的结果是：
1，2,2a，10,10a,10b,10c,11,12,15,22；我想要SQL语句代码，谢谢
个人的思路是，先对字段末尾字符时行判断，如果是字母，则将字母去掉后转为int型，若无字母，则直接转换为int型，然后对转换后的值进行排序。只是本人对SQL编程不懂，有木有大侠帮个忙。给高分啦。

------解决方案--------------------

SQL code

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
*/

求先数目字后字母的排序SQL语句

相关推荐