你可以使用 PATINDEX() 函数，如下所示:

You can do it using PATINDEX() function like below :

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

SQL Fiddle 演示

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

If you have numbers in middle of the string then you need to create small user defined function to get number from string and sort data based on that number like below :

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

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

You can sort data by :

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