tsql - 在 T-SQL 中像字符串一样排序数字

Question

我在表(称为抗原)中有字符串(称为等位基因)，但我无法按正确顺序排序。

等位基因的代表性样本集可能:-

• 01:01
• 01:02
• 02:01
• 04:01
• 09:01N
• 10:01
• 104:01
• 105:01
• 11:01N
• 03:01:01
• 03:01:02

我需要这些等位基因按以下顺序排列:-

• 01:01
• 01:02
• 02:01
• 03:01:01
• 03:01:02
• 04:01
• 09:01N
• 10:01
• 11:01N
• 104:01
• 105:01

我无法将等位基因排序为字符串，因为 104:01 和 105:01 会出现在 11:01 之前。

我无法删除“:”字符并按数字排序，因为这会将 03:01:01 和 03:01:02 放在末尾，因为数值分别为 30101 和 30102。

我对如何实现这一目标感到困惑，如果有任何建议，我将不胜感激。

干杯

Answer 1

假设最大字符数 between/before/after : 为 3，您可以使所有字符串值具有相同的长度和顺序，如下所示。虽然它看起来有点复杂!

Fiddle demo

;with cte as (
select val, charindex(':',val,1) index1, 
            charindex(':',val,charindex(':',val,1)+1) index2
from t
)
select val,right('000' + left(val,index1-1),3) + ':' +
           case when index2-index1>0 
                then  right('000' + substring(val,index1+1,index2-index1-1),3)
           else right('000' + substring(val,index1+1,len(val)),3) end + ':' +
           case when index2>0 
                then  right('000' + right(val, len(val) - index2),3)
           else '000' end As odr

from cte
order by odr

|      VAL |         ODR |
--------------------------
|    01:01 | 001:001:000 |
|    01:02 | 001:002:000 |
|    02:01 | 002:001:000 |
| 03:01:01 | 003:001:001 |
| 03:01:02 | 003:001:002 |
|    04:01 | 004:001:000 |
|   09:01N | 009:01N:000 |
|    10:01 | 010:001:000 |
|   11:01N | 011:01N:000 |
|   104:01 | 104:001:000 |
|   105:01 | 105:001:000 |