问题描述
我有一列包含这样的数据.破折号表示同一张发票的多份副本，必须按升序排序
I have a column that contains data like this. dashes indicate multi copies of the same invoice and these have to be sorted in ascending order
790711
790109-1
790109-11
790109-2
我必须按此数字升序对其进行排序，但是由于这是一个varchar字段，因此它按字母顺序进行排序
i have to sort it in increasing order by this number but since this is a varchar field it sorts in alphabetical order like this
790109-1
790109-11
790109-2
790711
为了解决这个问题，我尝试将-(破折号)替换为空，然后将其强制转换为数字，然后对该数字进行排序
in order to fix this i tried replacing the -(dash) with empty and then casting it as a number and then sorting on that
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个分割部分上按顺序订购
Someone suggested to me to split invoice id on the - (dash ) and order by on the 2 split parts
like =====> order by split1 asc,split2 asc (790109,1)
like=====> order by split1 asc,split2 asc (790109,1)
我认为这行得通，但我将如何拆分该列.
which would work i think but how would i split the column.
Internet上的各种分割函数是那些返回表的函数，在这种情况下，我将需要一个标量函数.
The various split functions on the internet are those that return a table while in this case i would be requiring a scalar function.
还有其他可以使用的方法吗?数据显示在网格视图中，默认情况下网格视图不支持对2列进行排序(尽管我可以实现它:))，因此，如果有任何更简单的方法，我会非常好.
Are there any other approaches that can be used? The data is shown in grid view and grid view doesn't support sorting on 2 columns by default ( i can implement it though :) ) so if any simpler approaches are there i would be very nice.
编辑:感谢您的所有回答.虽然每个答案都是正确的，但我选择了答案，使我可以将这些列合并到GridView排序中，而对SQL查询的重构最少.
EDIT : thanks for all the answers. While every answer is correct i have chosen the answer which allowed me to incorporate these columns in the GridView Sorting with minimum re factoring of the sql queries.
推荐答案
明智地使用REVERSE，CHARINDEX和SUBSTRING，可以为我们提供所需的东西.我在下面的代码中使用了希望说明的列名来说明正在发生的事情.
Judicious use of REVERSE, CHARINDEX, and SUBSTRING, can get us what we want. I have used hopefully-explanatory columns names in my code below to illustrate what's going on.
设置示例数据:
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
这是代码.我have不安地想简化最终的表达方式.
And here's the code. I have a nagging feeling the final expressions could be simplified.
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子句，其余只是为了展示我的工作，就像这样:
Note that all you actually need is the ORDER BY clause, the rest is just to show my working, which goes like this:
- 反转字符串，找到连字符，获取连字符后的子字符串，反转该部分:这是没有任何缀名的数字
- 长度(不带任何后缀的数字)告诉我们从一开始要删除多少个字符，以便获得包括连字符在内的后缀.放置一个附加字符以获取数字部分，然后将其转换为
int.幸运的是，我们与SQL Server有所不同，该转换为空字符串提供了零. - 最后，有了这两部分，我们简单地
ORDER BY(不带任何后缀的数字)，然后乘以(后缀的数值).这是我们寻求的最终订单.
- Reverse the string, find the hyphen, get the substring after the hyphen, reverse that part: This is the number without any affix
- The length of (the number without any affix) tells us how many characters to drop from the start in order to get the affix including the hyphen. Drop an additional character to get just the numeric part, and convert this to
int. Fortunately we get a break from SQL Server in that this conversion gives zero for an empty string. - Finally, having got these two pieces, we simple
ORDER BY(the number without any affix) and then by (the numeric value of the affix). This is the final order we seek.
如果SQL Server允许我们说SUBSTRING(value, start)以便从那时开始获取字符串，则代码将更加简洁，但事实并非如此，因此我们不得不多说SUBSTRING(value, start, LEN(value)).
The code would be more concise if SQL Server allowed us to say SUBSTRING(value, start) to get the string starting at that point, but it doesn't, so we have to say SUBSTRING(value, start, LEN(value)) a lot.
这篇关于在SQL Server中将字符串排序为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！