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

Question

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

sqlsortingsql-server-2008-r2natural-sort

9

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

我需要按照这个数字的顺序将它升序排序，但由于这是一个varchar字段，它会按字母顺序排序，如下所示：

为了解决这个问题，我尝试将“-”替换为空，并将其转换为数字，然后进行排序。

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)

有人建议我在 -（破折号）上拆分发票ID并按2个拆分部分排序，例如=====> order by split1 asc,split2 asc (790109,1)。我认为这会起作用，但我该如何拆分列呢？互联网上的各种拆分函数都返回表格，而在这种情况下，我需要一个标量函数。还有其他可以使用的方法吗？数据以网格视图显示，网格视图默认不支持按2个列排序（尽管我可以实现它：)），因此如果有任何更简单的方法，那将非常好。编辑：感谢所有答案。虽然每个答案都是正确的，但我选择了允许我将这些列合并到GridView排序中，并最小限度地重构SQL查询的答案。

- Pankaj Kumar

1

我认为这充分说明了为什么您存储的值应该改变... - Clockwork-Muse

你为什么想让790109-11排在790109-2之前？ - Old Pro

9个回答

4

尝试这个 -

查询:

DECLARE @Invoice TABLE (InvoiceNumber VARCHAR(10))
INSERT @Invoice 
VALUES
      ('790711')
    , ('790709-1')
    , ('790709-21')
    , ('790709-11')
    , ('790709-211')
    , ('790709-2')

;WITH cte AS 
(
    SELECT 
          InvoiceNumber
        , lenght = LEN(InvoiceNumber)
        , delimeter = CHARINDEX('-', InvoiceNumber)
    FROM @Invoice
)
SELECT InvoiceNumber
FROM cte
CROSS JOIN (
    SELECT repl = MAX(lenght - delimeter)
    FROM cte
    WHERE delimeter != 0
) mx
ORDER BY 
      SUBSTRING(InvoiceNumber, 1, ISNULL(NULLIF(delimeter - 1, -1), lenght))
    , RIGHT(REPLICATE('0', repl) + SUBSTRING(InvoiceNumber, delimeter + 1, lenght), repl)

输出：

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

- Devart

3

试试这个

SELECT invoiceid FROM Invoice
ORDER BY 
CASE WHEN PatIndex('%[-]%',invoiceid) > 0
      THEN LEFT(invoiceid,PatIndex('%[-]%',invoiceid)-1)
      ELSE invoiceid END * 1
,CASE WHEN PatIndex('%[-]%',REVERSE(invoiceid)) > 0
      THEN RIGHT(invoiceid,PatIndex('%[-]%',REVERSE(invoiceid))-1)
      ELSE NULL END * 1

SQLFiddle演示

上面的查询使用了两个case语句：

按照发票号790109-1的第一部分进行排序（例如：790709）。
将发票号790109-1从“-”处分割，然后按照第二部分进行排序（例如：1）。

如需详细了解，请查看下面的SQLfiddle：

SQLFiddle详细演示

或使用“CHARINDEX”。

SELECT invoiceid FROM Invoice
ORDER BY 
CASE WHEN CHARINDEX('-', invoiceid) > 0
      THEN LEFT(invoiceid, CHARINDEX('-', invoiceid)-1)
      ELSE invoiceid END * 1
,CASE WHEN CHARINDEX('-', REVERSE(invoiceid)) > 0
      THEN RIGHT(invoiceid, CHARINDEX('-', REVERSE(invoiceid))-1)
      ELSE NULL END * 1

- bvr

2

按照每个部分单独排序是最简单可靠的方法，为什么要寻找其他方法？看一下这个简单的查询。

select *
from Invoice
order by Convert(int, SUBSTRING(invoiceid, 0, CHARINDEX('-',invoiceid+'-'))) asc,
         Convert(int, SUBSTRING(invoiceid, CHARINDEX('-',invoiceid)+1, LEN(invoiceid)-CHARINDEX('-',invoiceid))) asc

- Khanh TO

2

这里有很多好的答案，但我认为下面这个可能是最紧凑有效的order by子句：

SELECT *
FROM Invoice
ORDER BY LEFT(InvoiceId,CHARINDEX('-',InvoiceId+'-'))
         ,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))AS INT)DESC

演示：- SQL Fiddle

请注意，我在我的测试中添加了“790709”版本，因为这里列出的一些方法没有将无后缀版本视为低于有后缀版本。

如果您的发票编号长度不同，在“-”之前，那么您需要：

SELECT *
FROM Invoice
ORDER BY CAST(LEFT(list,CHARINDEX('-',list+'-')-1)AS INT)
         ,CAST(RIGHT(InvoiceId,CHARINDEX('-',REVERSE(InvoiceId)+'-'))AS INT)DESC

在破折号之前长度不同的演示：SQL Fiddle

（该链接为英文原文）

- Hart CO

1

我的版本：

declare @Len int
select @Len = (select max (len (invoiceid) -  charindex ( '-', invoiceid))-1 from MyTable)

select 
invoiceid ,
cast (SUBSTRING (invoiceid ,1,charindex ( '-', invoiceid )-1) as int) * POWER (10,@Len) + 
cast (right(invoiceid, len (invoiceid) -  charindex ( '-', invoiceid)  ) as int )
from MyTable

您可以将此实现为表格的新列：

ALTER TABLE MyTable ADD COLUMN invoice_numeric_id int null
GO

declare @Len int
select @Len = (select max (len (invoiceid) -  charindex ( '-', invoiceid))-1 from MyTable)


UPDATE TABLE MyTable
SET  invoice_numeric_id = cast (SUBSTRING (invoiceid ,1,charindex ( '-', invoiceid )-1) as int) * POWER (10,@Len) + 
    cast (right(invoiceid, len (invoiceid) -  charindex ( '-', invoiceid)  ) as int )

- Stoleg

1

一种方法是将InvoiceId拆分成其各个部分，然后按部分排序。这里我使用了一个派生表，但也可以使用CTE或临时表来完成。

select InvoiceId, InvoiceId1, InvoiceId2
from
(
    select
    InvoiceId,
    substring(InvoiceId, 0, charindex('-', InvoiceId, 0)) as InvoiceId1,
    substring(InvoiceId, charindex('-', InvoiceId, 0)+1, len(InvoiceId)) as InvoiceId2
    FROM Invoice
) tmp
order by
cast((case when len(InvoiceId1) > 0 then InvoiceId1 else InvoiceId2 end) as int),
cast((case when len(InvoiceId1) > 0 then InvoiceId2 else '0' end) as int)

在上面的代码中，InvoiceId1和InvoiceId2是InvoiceId的组成部分。外层的select包含了这些部分，但仅用于演示目的——在您的选择中不需要这样做。

派生表（内部的select）获取InvoiceId以及组成部分。它的工作方式如下：

- 当InvoiceId中有破折号时，InvoiceId1将包含数字的第一部分，InvoiceId2将包含第二部分。 - 当没有破折号时，InvoiceId1将为空，InvoiceId2将包含整个数字。

上述第二种情况（没有破折号）并不理想，因为理想情况下，InvoiceId1应该包含数字，InvoiceId2应为空。要使内部选择最佳工作会降低选择的可读性。我选择了非最佳的、更易读的方法，因为它足以允许排序。

这就是为什么ORDER BY子句会测试长度的原因 - 它需要处理上述两种情况。

演示请参见 SQL Fiddle

- chue x

1

将排序分为两个部分：

SQL Fiddle

MS SQL Server 2008架构设置:

CREATE TABLE TestData
(
  data varchar(20)
)

INSERT TestData
SELECT '790711' as data
UNION
    SELECT '790109-1'
UNION
    SELECT '790109-11'
UNION 
    SELECT '790109-2'

查询1:

SELECT *
FROM TestData
ORDER BY 
    FLOOR(CAST(REPLACE(data, '-', '.') AS FLOAT)),
    CASE WHEN CHARINDEX('-', data) > 0 
        THEN CAST(RIGHT(data, len(data) - CHARINDEX('-', data)) AS INT)
        ELSE 0 
    END

结果:

|      DATA |
-------------
|  790109-1 |
|  790109-2 |
| 790109-11 |
|    790711 |

- Steve Ford

0

尝试：

select invoiceid  ... order by Convert(decimal(18, 2), REPLACE(invoiceid, '-', '.'))

- johndsamuels

3

抱歉，那个方法不可行，因为它将-1和-10都转换为了790109.10。 - Pankaj Kumar

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- AakashM · Accepted Answer

明智地使用 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

以下是代码。我有一种不安的感觉，最终表达式可能可以简化。

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))。