如何在 SQL Server 中对包含数字的 VARCHAR 列进行排序? [英] How do I sort a VARCHAR column in SQL server that contains numbers?

问题描述

我在 SQL Server 2000 数据库中有一个 VARCHAR 列，它可以包含字母或数字.这取决于如何在前端为客户配置应用程序.

I have a VARCHAR column in a SQL Server 2000 database that can contain either letters or numbers. It depends on how the application is configured on the front-end for the customer.

当它确实包含数字时，我希望它按数字排序，例如作为1"、2"、10"而不是1"、10"、2".仅包含字母或字母和数字(例如A1")的字段可以按字母顺序正常排序.例如，这将是可接受的排序顺序.

When it does contain numbers, I want it to be sorted numerically, e.g. as "1", "2", "10" instead of "1", "10", "2". Fields containing just letters, or letters and numbers (such as 'A1') can be sorted alphabetically as normal. For example, this would be an acceptable sort order.

1
2
10
A
B
B1

实现这一目标的最佳方法是什么?

What is the best way to achieve this?

推荐答案

一种可能的解决方案是在数值前面填充一个字符，使所有的字符串长度相同.

One possible solution is to pad the numeric values with a character in front so that all are of the same string length.

以下是使用该方法的示例:

Here is an example using that approach:

select MyColumn
from MyTable
order by 
    case IsNumeric(MyColumn) 
        when 1 then Replicate('0', 100 - Len(MyColumn)) + MyColumn
        else MyColumn
    end

100 应替换为该列的实际长度.

The 100 should be replaced with the actual length of that column.

这篇关于如何在 SQL Server 中对包含数字的 VARCHAR 列进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！