您可以使用PATINDEX（）函数执行此操作，如下所示：

select * from Test 
order by CAST(SUBSTRING(Name + '0', PATINDEX('%[0-9]%', Name + '0'), LEN(Name + '0')) AS INT)

SQL Fiddle演示

如果字符串中间有数字，则需要创建一个小的用户定义函数，以从字符串中获取数字，并基于该数字对数据进行排序，如下所示：

CREATE FUNCTION dbo.fnGetNumberFromString (@strInput VARCHAR(255)) 
RETURNS VARCHAR(255) 
AS 
BEGIN 
    DECLARE @intNumber int 
    SET @intNumber = PATINDEX('%[^0-9]%', @strInput)

    WHILE @intNumber > 0
    BEGIN 
        SET @strInput = STUFF(@strInput, @intNumber, 1, '')
        SET @intNumber = PATINDEX('%[^0-9]%', @strInput)
    END 

    RETURN ISNULL(@strInput,0) 
END 
GO

您可以按以下方式对数据进行排序：

select Name from Test order by dbo.fnGetNumberFromString(Name), Name

• （基于@shenhengbin和@EchO对this question的回答）*

下面是我所说的“干净的黑客”。假设你在Col1列上订购：

ORDER BY LEN(Col1), Col1

这是一个黑客，虽然我个人感到自豪使用它。

在order by语句中，当值中包含任何数字时，请在前面加上足够多零，以使所有字母数字值长度相同

SELECT ColName
FROM TableName
ORDER BY 
 CASE WHEN ColName like '%[0-9]%' 
 THEN Replicate('0', 100 - Len(ColName)) + ColName
 ELSE ColName  END

您可以删除前三个字符，并将其余字符转换为int

SELECT Value,
       Num=CAST(RIGHT(Value, LEN(Value) - 3) AS int)
FROM dbo.TableName
ORDER BY Num

演示版

您可以修改this answer中的函数 RemoveNonAlphaCharacters，以过滤掉除数字以外的所有内容，然后使用ORDER BY来使用该函数。

此查询仅使用标准SQL说明如何按ODER BY对字符串开头或结尾包含的数字进行排序。此查询还说明如何查看字符串的其余“内部”部分，以查看其中是否包含任何数字。如果需要进一步处理，了解字符串中是否包含数字将非常有用。

WITH stringNumberData AS
(   /* Build up Fake data with Numbers at the Beginning, End and Middle of the string */
    SELECT 1  AS uniqueKey, 'ABC'      AS NumberFromString UNION ALL
    SELECT 2  AS uniqueKey, 'ABC1'     AS NumberFromString UNION ALL
    SELECT 3  AS uniqueKey, 'ABC2'     AS NumberFromString UNION ALL
    SELECT 4  AS uniqueKey, 'ABC3'     AS NumberFromString UNION ALL
    SELECT 5  AS uniqueKey, 'ABC10'    AS NumberFromString UNION ALL
    SELECT 6  AS uniqueKey, 'ABC11'    AS NumberFromString UNION ALL
    SELECT 7  AS uniqueKey, 'ABC12'    AS NumberFromString UNION ALL
    SELECT 8  AS uniqueKey, 'ABC20'    AS NumberFromString UNION ALL
    SELECT 9  AS uniqueKey, 'ABC21'    AS NumberFromString UNION ALL
    SELECT 10 AS uniqueKey, 'ABC22'    AS NumberFromString UNION ALL
    SELECT 11 AS uniqueKey, 'ABC30'    AS NumberFromString UNION ALL
    SELECT 12 AS uniqueKey, 'ABC31'    AS NumberFromString UNION ALL
    SELECT 13 AS uniqueKey, 'ABC32'    AS NumberFromString UNION ALL
    SELECT 14 AS uniqueKey, '1ABC'     AS NumberFromString UNION ALL
    SELECT 15 AS uniqueKey, '2ABC'     AS NumberFromString UNION ALL
    SELECT 16 AS uniqueKey, '3ABC'     AS NumberFromString UNION ALL
    SELECT 17 AS uniqueKey, '10ABC'    AS NumberFromString UNION ALL
    SELECT 18 AS uniqueKey, '11BC'     AS NumberFromString UNION ALL
    SELECT 19 AS uniqueKey, '12ABC'    AS NumberFromString UNION ALL
    SELECT 20 AS uniqueKey, '10ABC18'  AS NumberFromString UNION ALL
    SELECT 21 AS uniqueKey, '11BC52'   AS NumberFromString UNION ALL
    SELECT 22 AS uniqueKey, '12ABC42'  AS NumberFromString UNION ALL
    SELECT 23 AS uniqueKey, 'A3BC18'   AS NumberFromString UNION ALL
    SELECT 24 AS uniqueKey, 'B3C52'    AS NumberFromString UNION ALL
    SELECT 25 AS uniqueKey, '12AB3C'   AS NumberFromString UNION ALL
    SELECT 26 AS uniqueKey, 'A3BC'     AS NumberFromString UNION ALL
    SELECT 27 AS uniqueKey, 'AB2C'     AS NumberFromString UNION ALL
    SELECT 28 AS uniqueKey, 'ABC85D'   AS NumberFromString

)   

SELECT d.uniqueKey, d.NumberFromString
    /* Extract numerical values contained on the LEFT of the String by finding the index of the first non number */
    , LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS 'Left Numbers Extraction'

    /* Extract numerical data contained on the RIGHT of the String */
    , RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS 'Right Numbers Extraction'

    /* The below checks inside the Inner string to determine if numbers exists within it.  Could be used for further processing if further extraction is necessary */
    , PATINDEX('%[0-9]%',
        SUBSTRING(d.NumberFromString /*, Start Pos, Length to Extract) */
            , PATINDEX('%[^0-9]%', d.NumberFromString)  /* Start Pos is first left non number */
            /* The below obtains the length of the Inner String so it can be extracted */
            , LEN(d.NumberFromString) - ((PATINDEX('%[^0-9]%', d.NumberFromString) -1 )) -  (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1) /* (String Length) - (LEFT Numbers) - (RIGHT Numbers) */
      )) AS innerNumExists

    /* The two lines below tell us if there exists a number at the Beginning and/or End of the string */
    , PATINDEX('%[0-9]%',  LEFT(d.NumberFromString, 1)) AS leftNumExists
    , PATINDEX('%[0-9]%', RIGHT(d.NumberFromString, 1)) AS rightNumExists

    /* Locates and returns the index of the very first number located in the string from Left to Right */
    , PATINDEX('%[^0-9]%', d.NumberFromString) AS firstLeftNonNum_index

    /* Locates and returns the index of the very first number located in the string from Right to Left */
    , LEN(d.NumberFromString) - (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 ) AS firstRightNonNum_index

    /* Get the length of the numbers existing from Right to Left up to the first non numeric character */   
    , PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 AS rightStringLen

FROM stringNumberData d
ORDER BY 
    /* Ordering first by numbers found on the LEFT of the string */
    CAST(LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS INT )
    /* Ordering second by numbers found on the RIGHT of the string */
    , CAST(RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS INT )
;

下面是一个简单易懂的示例，适用于SQL Server 17+的使用者。

DECLARE @Data table ( val varchar(10) );
INSERT INTO @Data VALUES
    ( 'ABC' ),( 'ABC1' ),( 'ABC11' ),( 'ABC12' ),( 'ABC13' ),( 'ABC2' ), ( 'B1C' ),
    ( 'ABC20' ),( 'ABC21' ),( 'ABC22' ),( 'ABC3' ),( 'ABC31' ),( 'ABC32' );

SELECT val FROM @Data AS d
CROSS APPLY (
    SELECT CAST ( TRANSLATE ( d.val, 'abcdefghijklmnopqrstuvwxyz', '                          ' ) AS int ) AS Num
) AS x
ORDER BY
    LEFT ( val, 1 ), Num;

退货

+-------+
|  val  |
+-------+
| ABC   |
| ABC1  |
| ABC2  |
| ABC3  |
| ABC11 |
| ABC12 |
| ABC13 |
| ABC20 |
| ABC21 |
| ABC22 |
| ABC31 |
| ABC32 |
| B1C   |
+-------+

SQL Server的TRANSLATE采用三个参数：inputString, characters, translations .
在本例中，inputString是列名。
characters是要替换的值，在本例中是字母表。
translations是要替换的值。该字符串的长度必须等于characters--因此空字符串的长度为26个空格。
最后，使用CAST忽略空格，并允许将剩余的值作为int进行排序。
您可以在此处阅读有关TRANSLATE的信息：
https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15