在SQL Server中将字符串排序为数字

Question

我有一列包含这样的数据。破折号表示同一张发票的多份副本，这些副本必须按升序排序

790711
790109-1
790109-11
790109-2

我必须按此数字以升序对其进行排序，但是由于这是一个varchar字段，因此它按字母顺序进行排序，如下所示

790109-1
790109-11
790109-2
790711

为了解决这个问题，我尝试将-（破折号）替换为空，然后将其强制转换为数字，然后对该数字进行排序

select cast(replace(invoiceid,'-','') as decimal) as invoiceSort...............order by invoiceSort asc

虽然这更好，并像这样排序

            invoiceSort
790711      (790711)   <-----this is wrong now as it should come later than 790109
790109-1    (7901091)
790109-2    (7901092)
790109-11   (79010911)

有人建议我在-（破折号）上分割发票编号，并在2个分割部分上按顺序排列

喜欢=====> order by split1 asc,split2 asc (790109,1)

我认为这将起作用，但我将如何拆分列。

互联网上的各种分割函数是那些返回表的函数，在这种情况下，我将需要一个标量函数。

还有其他方法可以使用吗？数据显示在网格视图中，默认情况下网格视图不支持对2列进行排序（尽管我可以实现它:)），因此，如果有任何更简单的方法，我会非常好。

编辑 ：感谢所有的答案。虽然每个答案都是正确的，但我选择了答案，使我可以将这些列合并到GridView排序中，而对SQL查询的重构最少。

Answer 1

明智地使用的REVERSE，CHARINDEX以及SUBSTRING，可以得到我们想要的东西。我在下面的代码中使用了希望说明的列名来说明正在发生的事情。

设置样本数据：

DECLARE @Invoice TABLE (
    InvoiceNumber nvarchar(10)
);

INSERT @Invoice VALUES
('790711')
,('790709-1')
,('790709-11')
,('790709-21')
,('790709-212')
,('790709-2')

SELECT * FROM @Invoice

样本数据：

InvoiceNumber
-------------
790711
790709-1
790709-11
790709-21
790709-212
790709-2

这是代码。我有一个the的感觉，可以简化最终的表达方式。

SELECT 
    InvoiceNumber
    ,REVERSE(InvoiceNumber) 
        AS Reversed
    ,CHARINDEX('-',REVERSE(InvoiceNumber)) 
        AS HyphenIndexWithinReversed
    ,SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber)) 
        AS ReversedWithoutAffix
    ,SUBSTRING(InvoiceNumber,1+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber)) 
        AS AffixIncludingHyphen
    ,SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber)) 
        AS AffixExcludingHyphen
    ,CAST(
        SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
        AS int)  
        AS AffixAsInt
    ,REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))) 
        AS WithoutAffix
FROM @Invoice
ORDER BY
    -- WithoutAffix
    REVERSE(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))) 
    -- AffixAsInt
    ,CAST(
        SUBSTRING(InvoiceNumber,2+LEN(SUBSTRING(REVERSE(InvoiceNumber),1+CHARINDEX('-',REVERSE(InvoiceNumber)),LEN(InvoiceNumber))),LEN(InvoiceNumber))
        AS int)

输出：

InvoiceNumber Reversed   HyphenIndexWithinReversed ReversedWithoutAffix AffixIncludingHyphen AffixExcludingHyphen AffixAsInt  WithoutAffix
------------- ---------- ------------------------- -------------------- -------------------- -------------------- ----------- ------------
790709-1      1-907097   2                         907097               -1                   1                    1           790709
790709-2      2-907097   2                         907097               -2                   2                    2           790709
790709-11     11-907097  3                         907097               -11                  11                   11          790709
790709-21     12-907097  3                         907097               -21                  21                   21          790709
790709-212    212-907097 4                         907097               -212                 212                  212         790709
790711        117097     0                         117097                                                         0           790711

请注意，您实际需要的只是该ORDER BY子句，其余的只是为了展示我的工作，它是这样的：

• 反转字符串，找到连字符，获取连字符后的子字符串，反转该部分：这是没有任何缀名的数字
• 的长度（不带任何后缀的数字）告诉我们从一开始要删除多少个字符，以便获得包括连字符在内的后缀。放置一个附加字符以获取数字部分，然后将其转换为int。幸运的是，我们与SQL Server有所不同，该转换为空字符串提供了零。
• 最后，在获得了这两部分之后，我们简单地进行操作ORDER BY（不带任何后缀的数字），然后乘以（后缀的数字值）。这是我们寻求的最终订单。

如果SQL Server允许我们说SUBSTRING(value, start)从那时开始获取字符串，则代码将更加简洁，但是事实并非如此，因此我们不得不说SUBSTRING(value, start,LEN(value))很多。