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

Question

我要排序字符串不共享相同的格式，我遇到了麻烦，找到一个解决方案。在stackoverflow上尝试了几个选项，但它不适合我，因为它们是针对特定格式的数据。

下面是一个我必须排序的数据示例。

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

我用ORDER BY

得到了这样的结果

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设计的，特别是SQL Server。

您正在尝试从字符串的两个部分提取一个数字并按该数字排序。Arrgg !这实际上表明您在一个字符串中存储了多种类型的信息——这些信息在单独的列中可能会更好地表示。

也就是说，你可以做你想做的。下面是一个处理问题中提供的数据的方法:

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<>小提琴

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