SQL Server 2008 - 按带有数字的字符串排序 [英] SQL Server 2008 - order by strings with number numerically

问题描述

我的表中有以下值:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

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

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

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

When I do select * from table order by my column asc I get following results:

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

我需要按数字排序:

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

如何实现?

推荐答案

你可以使用 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

这篇关于SQL Server 2008 - 按带有数字的字符串排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！