SQL 服务器:字符串转换数字和排序问题

Question

当我选择时，有一列要显示。

我有以下示例代码

C*12:03:01:01
C*12:03:01:02
C*12:03:06
C*12:109
C*12:110
C*12:111
C*12:23

它应该有：分隔符。

当它排序时，它应该对每个级别进行排序

例如第一级 12 都是一样的，所以排序相同第二级03，03，23，109，110，111之类的数字，然后是第三...

所以它应该如下所示

C*12:03:01:01
C*12:03:01:02
C*12:03:06
C*12:23
C*12:109
C*12:110
C*12:111

我尝试删除所有字符并仅显示具有以下功能的数字

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'
    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')
RETURN @String
END

当我尝试时

select dbo.fn_StripCharacters(DATA.AlleleName, '^0-9') 
from @temp
where CAST(dbo.fn_StripCharacters(DATA.AlleleName, '^0-9') AS INT)

然后它将显示

1223
12109
12110
12111
120306
12030101
12030102

所以我改变了主意，每个级别（由：分隔）最多只有 3 个字符。

你们知道制作方法

012:003:001:001
012:003:001:002
012:003:006:000
012:109:000:000
012:110:000:000
012:111:000:000
012:023:000:000

然后如果我删除 ： 并排序，也许它会起作用。

你们对此有什么更好的主意吗？谢谢！

Answer 1

在你的例子中，我认为你可以让parsename()为你做这项工作：

order by parsename(replace(col, ':', '.'), 4),
         cast(parsename(replace(col, ':', '.'), 3) as int),
         cast(parsename(replace(col, ':', '.'), 2) as int),
         cast(parsename(replace(col, ':', '.'), 1) as int)

您的示例都有四个部分，因此这应该有效。 此外，第一个元素看起来（从您的示例中）可以按字母顺序排序。

Answer 2

您可以编写一个函数，该函数将返回由给定位置的某个分隔符拆分的字符串的一部分。例：

alter function [dbo].[f_Split]
    (
        @List nvarchar(max),
        @SplitOn varchar(5),
        @Position int
    )  
    RETURNS nvarchar(250)
    AS  
    BEGIN 
        declare @Index int = 0
        declare @Value nvarchar(250) = ''
        while (Charindex(@SplitOn, @List) > 0) and @Index != @Position
            begin
                set @Value = ltrim(rtrim(substring(@List,1,charindex(@SplitOn,@List)-1)))
                set @List = substring(@List,charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
                set @Position = @Position - 1
            end
        if @Index != @Position
            set @Value = ''
        return @Value
    END
GO

然后，您可以在查询中使用它，如下所示：

declare @temp table (col varchar(50))
insert into @temp (col) values ('C*12:03:01:01')
insert into @temp (col) values ('C*12:03:01:02')
insert into @temp (col) values ('C*12:03:06')
insert into @temp (col) values ('C*12:23')
insert into @temp (col) values ('C*12:109')
insert into @temp (col) values ('C*12:110')
insert into @temp (col) values ('C*12:111')
select 
    *
from 
    @temp 
order by
    dbo.f_split('C*12:03:01:01', ':', 5),
    dbo.f_split('C*12:03:01:01', ':', 4),
    dbo.f_split('C*12:03:01:01', ':', 3),
    dbo.f_split('C*12:03:01:01', ':', 2),
    dbo.f_split('C*12:03:01:01', ':', 1)