非特定格式的SQL字母数字排序

Question

我必须对不共享相同格式的字符串进行排序，并且我很难为这个字符串找到解决方案。为此在堆栈溢出上尝试了几个选项，但是它对我不起作用，因为它们适用于特定的格式化数据。

下面是我必须对数据进行排序的一个例子。

12-ABC
1-ABC
ABC-10
ABC-11
ABC-100
2-ABCD
ABC-100A

我得到了这样的结果

1-ABC
12-ABC
2-ABCD
ABC-10
ABC-100
ABC-100A
ABC-11

但我想要这个

1-ABC
2-ABCD
12-ABC
ABC-10
ABC-11
ABC-100
ABC-100A

你会怎么做？

Answer 1

这是一个复杂的问题。解析字符串通常不是为SQL设计的，尤其是Server。

您正在尝试从字符串的两个部分提取一个数字，并按该数字进行排序。阿格！这确实表明，您正在一个字符串中存储多种类型的信息--这些信息可能最好用单独的列来表示。

也就是说，你可以做你想做的事。下面是对您在问题中提供的数据进行处理的一个方法：

select t.str
from (values ('1-ABC'),
             ('12-ABC'),
             ('2-ABCD'),
             ('ABC-10'),
             ('ABC-100'),
             ('ABC-100A'),
             ('ABC-11')
     ) t(str) cross apply
     (values (left(str, charindex('-', str + '-') - 1), stuff(str, 1, charindex('-', str), ''))
     ) v(part1, part2)
order by coalesce(try_convert(int, v.part1), 999999999),
         part1,
         try_convert(int, left(v.part2, patindex('%[^0-9]%', v.part2 + 'x') - 1)),
         part2;

这里是db<>fiddle。

Answer 2

您可以将字符串拆分为数字和文本(在‘-’上)，然后按num列排序。类似于：

select iif((charindex('-', str) > 0), (cast(substring(str, 1, (charindex('-', str)-1)) as integer)), 9999) as num, str
from (
    select '1-ABC' as str union all
    select '2-ABC' as str  union all
    select '12-ABC' as str  union all
    select 'ABC' as str
) tbl
order by num, str

这样，您将首先按数值排序，然后再按字符串排序。在这里，我只是添加了一个非常高的数字到无数字值，所以它将被排序最后。

Answer 3

你所有的信息都是男的。我设法使它几乎完美地与这个特殊的情况。

select distinct PCE_NAM,
    --FIRST SECTION OF THE STRING
    --REPLICATE to fill 0s before numerics
    (CASE 
        WHEN CHARINDEX('-', PCE_NAM) = 0 then PCE_NAM
        WHEN CHARINDEX('-', PCE_NAM) > 0 and 
            ISNUMERIC(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)) = 1 and
            LEN(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)) <= 8
            then REPLICATE('0', 8-LEN(LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1))) + LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)
        ELSE LEFT(PCE_NAM, CHARINDEX('-', PCE_NAM) -1)
    END) as FirstPart,
    --SECOND SECTION OF THE STRING
    (CASE 
        WHEN CHARINDEX('-', PCE_NAM) = 0 then ''
        WHEN CHARINDEX('-', PCE_NAM) > 0 and 
            ISNUMERIC(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))) = 1 and
            LEN(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))) <= 8
            then REPLICATE('0', 8-LEN(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)))) + SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))
        ELSE SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))
    END) as SecondPart,
    --GET THE NUMERICS ONLY FROM THE SECOND SECTION SO CAN SORT PROPERLY
    (CASE 
        WHEN LEN(LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))) <= 8
            then REPLICATE('0', 8-LEN(
            LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)))))) + 
            LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))
        ELSE LEFT(SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM)), PATINDEX('%[0-9][^0-9]%', SUBSTRING(PCE_NAM, CHARINDEX('-', PCE_NAM)+1, LEN(PCE_NAM))))
    END) as SecondPartF
from PARTS
order by FirstPart, SecondPartF, SecondPart