使用字母数字条目对VARCHAR列进行排序 [英] Sorting VARCHAR column with alphanumeric entries

问题描述

我正在使用SQL Server，该列为VARCHAR(50)，我想像这样对它进行排序:

I am using SQL Server, the column is a VARCHAR(50) and I want to sort it like this:

1A        
1B        
2        
2        
3        
4A        
4B        
4C        
5A        
5B        
5C        
5N        
14 Draft        
21        
22A        
22B        
23A        
23B        
23C        
23D        
23E        
25        
26        
FR01584        
MISC

到目前为止，我的是:

Select *
From viewASD
ORDER BY 
    Case When IsNumeric(LEFT(asdNumNew,1)) = 1 
         Then CASE When IsNumeric(asdNumNew) = 1 
                   Then Right(Replicate('0',20) + asdNumNew + '0', 20)
                   Else Right(Replicate('0',20) + asdNumNew, 20) 
              END
         When IsNumeric(LEFT(asdNumNew,1)) = 0 
         Then Left(asdNumNew + Replicate('',21), 20)
    End

但是此SQL语句将'14 Draft'紧接在'26'之后.

But this SQL statement puts '14 Draft' right after '26'.

有人可以帮忙吗?谢谢

推荐答案

您的WHERE语句非常复杂.

Your WHERE statement is... oddly complex.

您似乎想按整数顺序对所有前导数字进行排序，然后再对其余部分进行排序.如果是这样，则应将其作为单独的子句进行操作，而不是尝试一并完成.您遇到的特定问题是，您只允许输入一个数字，而不是两个或多个. (而且没有两个东西.)

It looks like you want to sort by any leading numeric digits in integer order, and then sort by the remainder. If so, you should do that as separate clauses, rather than trying to do it all in one. The specific issue you're having is that you're only allowing for a single-digit number, instead of two or more. (And there's No such thing as two.)

这是您的解决方法，并使用两个单独的计算列以及 SQLFiddle 测试您的ORDER BY. (请注意，这假定asdNumNew的数字部分将适合T-SQL int.否则，您需要在第一个ELSE上调整CAST和最大值.)

Here's your fix, along with a SQLFiddle, using two separate calculated columns tests for your ORDER BY. (Note that this assumes the numeric portion of asdNumNew will fit in a T-SQL int. If not, you'll need to adjust the CAST and the maximum value on the first ELSE.)

SELECT * FROM viewASD
ORDER BY 
CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN CAST(asdNumNew as int)

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN CAST(
    LEFT(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) - 1
    ) as int)

  ELSE 2147483648
END, 


CASE 
  WHEN ISNUMERIC(asdNumNew)=1 
  THEN NULL

  WHEN PATINDEX('%[^0-9]%',asdNumNew) > 1 
  THEN SUBSTRING(
      asdNumNew,
      PATINDEX('%[^0-9]%',asdNumNew) ,
      50
    ) 

  ELSE asdNumNew
END

这篇关于使用字母数字条目对VARCHAR列进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！