SQL Server 2008 -用数字数字的字符串排序。

I have following values in my table:

我的表中有以下值:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

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.

所以基本上，我所拥有的是任何字符串值(不总是ABC，任何字符串值)，它们要么可以跟在数字后面，要么就是一个没有数字的字符串。

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

当我用我的列asc从表订单中选择*时，我会得到以下结果:

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

I need it sorted numerically:

我需要对它进行数值排序:

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

How can this be accomplished?

这是如何实现的呢?

4 个解决方案

#1

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

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

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

SQL Fiddle Demo

SQL小提琴演示

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

#2

You could remove the first three characters and cast the rest to int

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

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

Demo

演示

#3

You could adapt the function RemoveNonAlphaCharacters in this answer to filter out everything except numbers, and then use an ORDER BY using that function.

您可以在这个答案中修改函数RemoveNonAlphaCharacters以过滤除数字之外的所有字符，然后使用该函数使用ORDER。

#4

In order by statement, prepend enough zeros with when value contains any number in it to make all alphanumerica value same length

按照语句的顺序，在前面加上足够的0和when值包含任何数字，使所有的字母数字都具有相同的长度

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

#1