sql-server - SQL Server 2008 - 按带有数字的字符串排序

Question

我的表中有以下值:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

所以基本上我拥有的是任何字符串值(不总是 ABC，任何字符串值)，它可以后跟数字，也可能只是一个不带数字的字符串。

当我按列 asc 从表顺序中选择 * 时，我得到以下结果:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

我需要按数字排序:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

如何实现这一点？

Answer 1

您可以使用 PATINDEX() 函数来完成此操作，如下所示:

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

<强> SQL Fiddle Demo

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

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