sql中以‘-’为分割进行排序

Question

请帮解决个问题
我有一个AAA表中字段NUM为NVARCHAR 值为
101-2-1-4-611
1202-3-2-111
1020-A-3
1020-1-1-101
1020-10-2-201
1020-2-2-201
120-21-3-420想查询表 全部记录 并按此字段排序
SELECT * FROM AAA order by NUM想实现查询结果按下面这行结果
101-2-1-4-611
120-21-3-420
1020-1-1-101
1020-2-2-201
1020-10-2-201
1020-A-3
1202-3-2-111就是说以  '－'  为基准（分割）
从左开始 进行排序先比较第一个，再比较第二个，再比较第三个....直至完成，每一项的按先数字后字母的原则我的解决思路（参考）
如果  '-'不足4个则补'-0'直至补齐4个如
1020-A-3  则变为1020-A-3-0-0
每一项的按先数字后字母的原则，就是说从0开始，最后是字母，如
1020-A-3 与1020-1-1-101 进行比较
则为 1020-A-3-0-0 与1020-1-1-101-0先比较第一个，再比较第二个，数字1为先，A为后，则排序为
1020-1-1-101
1020-A-3现在我只能实现为101-2-1-4-611
120-21-3-420
1020-1-1-101
1020-10-2-201 //由于第二项按字符排序出现问题
1020-2-2-201
1020-A-3
1202-3-2-111 

Answer 1

SQL按书章节目录排序
CREATE TABLE tb (ID INT IDENTITY(1,1),Code VARCHAR(20))
GO
INSERT tb SELECT '1'   
UNION ALL SELECT '2'   
UNION ALL SELECT '3'   
UNION ALL SELECT '1.1'  
UNION ALL SELECT '2.1'
UNION ALL SELECT '131.2.1.11.1.131111'   
UNION ALL SELECT '2.2'  
UNION ALL SELECT '121.2.1.1.1.131111' 
UNION ALL SELECT '3.1'  
UNION ALL SELECT '3.2'  
UNION ALL SELECT '20.1' 
UNION ALL SELECT '1.1.1' 
UNION ALL SELECT '1.1.2' 
UNION ALL SELECT '1.1.3'
UNION ALL SELECT '1.1.11.1'
UNION ALL SELECT '1.1.2.1'
UNION ALL SELECT '2.1.2.1'
GO
CREATE FUNCTION dbo.ReplaceCode(@code VARCHAR(20))
    RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @re VARCHAR(8000),@tempStr VARCHAR(100)
    SELECT @re='',@tempStr=''
    
    WHILE CHARINDEX('.',@code)>0
    BEGIN
        SELECT @tempStr=LEFT(@code,CHARINDEX('.',@code)-1),
               @re=@re+RIGHT('0000000000'+@tempStr,10),
               @code=STUFF(@code,1,CHARINDEX('.',@code),'')
    END
    IF LEN(@code)>0
        SET @re=@re+RIGHT('0000000000'+@code,10)
    RETURN @re
END
GO
SELECT *
FROM tb
ORDER BY dbo.ReplaceCode(code)
DROP TABLE tb
DROP FUNCTION ReplaceCode
 

Answer 2

CREATE TABLE tb (ID INT IDENTITY(1,1),NUM VARCHAR(20))
GO
INSERT tb SELECT '101-2-1-4-611'   
UNION ALL SELECT '1202-3-2-111'   
UNION ALL SELECT '1020-A-3'   
UNION ALL SELECT '1020-1-1-101'  
UNION ALL SELECT '1020-10-2-201'
UNION ALL SELECT '1020-2-2-201'   
UNION ALL SELECT '120-21-3-420'
GOCREATE FUNCTION dbo.ReplaceCode(@code VARCHAR(100))
RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @re VARCHAR(8000),@tempStr VARCHAR(100)
    SELECT @re='',@tempStr=''
set @code=@code+replicate('-0',4-len(@code)+len(replace(@code,'-','')))+'-'  --不足4个的补足4个，最后加一个'-'是为了简化循环
    
    WHILE len(@code)>0
    BEGIN
        SELECT @tempStr=LEFT(@code,CHARINDEX('-',@code)-1),
               @re=@re+RIGHT(100000000+case isnumeric(@tempStr) when 1 then @tempStr else 100000+ascii(@tempStr) end,8), --对于非数字，取它的ascii码+一个很大数
               @code=STUFF(@code,1,CHARINDEX('-',@code),'')
    END
    RETURN @re
END
GOSELECT * FROM tb order by dbo.ReplaceCode(NUM)
/*
---------
1 101-2-1-4-611
7 120-21-3-420
4 1020-1-1-101
6 1020-2-2-201
5 1020-10-2-201
3 1020-A-3
2 1202-3-2-111
*/DROP TABLE tb
DROP FUNCTION ReplaceCode

Answer 3

----根据小梁修改CREATE TABLE tb (ID INT IDENTITY(1,1),Code VARCHAR(20))
GO
INSERT tb SELECT '101-2-1-4-611 '   
UNION ALL SELECT '1202-3-2-111'   
UNION ALL SELECT '1020-A-3 '   
UNION ALL SELECT '1020-1-1-101'  
UNION ALL SELECT '1020-10-2-201'
UNION ALL SELECT '1020-2-2-201'   
UNION ALL SELECT '120-21-3-420'  GO
CREATE FUNCTION dbo.ReplaceCode(@code VARCHAR(20))
    RETURNS VARCHAR(8000)
AS
BEGIN
    DECLARE @re VARCHAR(8000),@tempStr VARCHAR(100)
    SELECT @re='',@tempStr=''
    
    WHILE CHARINDEX('-',@code)>0
    BEGIN
        SELECT @tempStr=LEFT(@code,CHARINDEX('-',@code)-1),
               @re=@re+ case when isnumeric(@tempStr) = 1 then RIGHT('0000000000'+@tempStr,10) else @tempStr end,
               @code=STUFF(@code,1,CHARINDEX('-',@code),'')
    END
    IF LEN(@code)>0
        SET @re=@re+RIGHT('0000000000'+@code,10)
    RETURN @re
END
GO
SELECT *
FROM tb
ORDER BY dbo.ReplaceCode(code)
DROP TABLE tb
DROP FUNCTION ReplaceCode