SQL命令固定长度的字母数字字符串，先由alpha-chars命令，然后再按数值排序。
SQL命令固定长度的字母数字字符串，先由alpha-chars命令，然后再按数值排序。
提问于 2022-10-10 03:16:34
在Server中按数值排序之前，如何按alpha字符对下列数据进行排序？
“00141W703”、“001413541”、“00141B204”、“00141A586”、“00900W738”、“0075W0825”、“001C5A389”
预期结果
'001C5A389'
'00141A586'
'00141B204'
'00141W703'
'001413541'
'0075W0825'
'00900W738'前面提出的所有类似问题都是针对特定的字符串模式的，其中要排序/排序的数据要么以alpha-chars/数值开头。但是在这种情况下，要排序的字符串是固定长度的，但是alpha字符和数值的位置是完全随机的。这几乎就像他们按照字母字符的顺序从左到右排序，先按升序排列，然后再按升序排列数值。
A样本数据集如下：
CREATE TABLE tab
(col varchar(9));
INSERT INTO tab
(col)
VALUES
('00141W703'),
('001413541'),
('00141B204'),
('00141A586'),
('00900W738'),
('0075W0825'),
('001C5A389');我很感激所提供的解决方案是在https://dbfiddle.uk/qHEWHO4F上测试的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-10-10 05:05:47
这是一个非常粗糙，蛮力的方法，肯定会有一个更好的解决方案。
基本上，您按每列进行排序，首先如果是alpha字符，然后是第二次，如果是数字字符。
使用"z“和"9”确保最后出现空。
select *
from tab
order by
case when isnumeric(substring(col,1,1)) = 0 then substring(col,1,1) else 'z' end asc,
case when isnumeric(substring(col,1,1)) = 1 then substring(col,1,1) else '9' end asc,
case when isnumeric(substring(col,2,1)) = 0 then substring(col,2,1) else 'z' end asc,
case when isnumeric(substring(col,2,1)) = 1 then substring(col,2,1) else '9' end asc,
case when isnumeric(substring(col,3,1)) = 0 then substring(col,3,1) else 'z' end asc,
case when isnumeric(substring(col,3,1)) = 1 then substring(col,3,1) else '9' end asc,
case when isnumeric(substring(col,4,1)) = 0 then substring(col,4,1) else 'z' end asc,
case when isnumeric(substring(col,4,1)) = 1 then substring(col,4,1) else '9' end asc,
case when isnumeric(substring(col,5,1)) = 0 then substring(col,5,1) else 'z' end asc,
case when isnumeric(substring(col,5,1)) = 1 then substring(col,5,1) else '9' end asc,
case when isnumeric(substring(col,6,1)) = 0 then substring(col,6,1) else 'z' end asc,
case when isnumeric(substring(col,6,1)) = 1 then substring(col,6,1) else '9' end asc,
case when isnumeric(substring(col,7,1)) = 0 then substring(col,7,1) else 'z' end asc,
case when isnumeric(substring(col,7,1)) = 1 then substring(col,7,1) else '9' end asc,
case when isnumeric(substring(col,8,1)) = 0 then substring(col,8,1) else 'z' end asc,
case when isnumeric(substring(col,8,1)) = 1 then substring(col,8,1) else '9' end asc,
case when isnumeric(substring(col,9,1)) = 0 then substring(col,9,1) else 'z' end asc,
case when isnumeric(substring(col,9,1)) = 1 then substring(col,9,1) else '9' end asc;实际上这条路有点整洁..。在ASCII值中为数字添加75，以给出比alpha字符更高的ASCII值。
select *
from tab
order by
case when isnumeric(substring(col,1,1)) = 0 then ascii(substring(col,1,1))
else ascii(substring(col,1,1)) + 75 end asc,
case when isnumeric(substring(col,2,1)) = 0 then ascii(substring(col,2,1))
else ascii(substring(col,2,1)) + 75 end asc,
case when isnumeric(substring(col,3,1)) = 0 then ascii(substring(col,3,1))
else ascii(substring(col,3,1)) + 75 end asc,
case when isnumeric(substring(col,4,1)) = 0 then ascii(substring(col,4,1))
else ascii(substring(col,4,1)) + 75 end asc,
case when isnumeric(substring(col,5,1)) = 0 then ascii(substring(col,5,1))
else ascii(substring(col,5,1)) + 75 end asc,
case when isnumeric(substring(col,6,1)) = 0 then ascii(substring(col,6,1))
else ascii(substring(col,6,1)) + 75 end asc,
case when isnumeric(substring(col,7,1)) = 0 then ascii(substring(col,7,1))
else ascii(substring(col,7,1)) + 75 end asc,
case when isnumeric(substring(col,8,1)) = 0 then ascii(substring(col,8,1))
else ascii(substring(col,8,1)) + 75 end asc,
case when isnumeric(substring(col,9,1)) = 0 then ascii(substring(col,9,1))
else ascii(substring(col,9,1)) + 75 end asc;再来一次比较整洁的：
with cte as (
select *
, ascii(substring(col,1,1)) order1
, ascii(substring(col,2,1)) order2
, ascii(substring(col,3,1)) order3
, ascii(substring(col,4,1)) order4
, ascii(substring(col,5,1)) order5
, ascii(substring(col,6,1)) order6
, ascii(substring(col,7,1)) order7
, ascii(substring(col,8,1)) order8
, ascii(substring(col,9,1)) order9
from tab
)
select col
from cte
order by
case when order1 < 65 then order1 + 75 else order1 end asc,
case when order2 < 65 then order2 + 75 else order2 end asc,
case when order3 < 65 then order3 + 75 else order3 end asc,
case when order4 < 65 then order4 + 75 else order4 end asc,
case when order5 < 65 then order5 + 75 else order5 end asc,
case when order6 < 65 then order6 + 75 else order6 end asc,
case when order7 < 65 then order7 + 75 else order7 end asc,
case when order8 < 65 then order8 + 75 else order8 end asc,
case when order9 < 65 then order9 + 75 else order9 end asc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74009906
复制相关文章
相似问题
腾讯云开发者
