SQL Server 2008 - порядок по строкам с численным числом

У меня есть следующие значения в моей таблице:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

Итак, в основном у меня есть любое строковое значение (не всегда ABC, любое строковое значение), за которым может следовать номер, или он может быть просто строкой без номера.

Когда я делаю выбор * из порядка таблицы по моему столбцу, я получаю следующие результаты:

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

Мне нужно отсортировать его численно:

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

Как это можно сделать?

Ответ 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

Ответ 2

Вы можете удалить первые три символа, а остальные - int

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

Демо

Ответ 3

Вы можете адаптировать функцию RemoveNonAlphaCharacters в этом ответе, чтобы отфильтровать все, кроме цифр, и затем использовать ORDER BY с помощью этой функции.

Ответ 4

В инструкции по порядку добавьте достаточное количество нулей, когда значение содержит любое число в нем, чтобы сделать все буквенно-числовое значение равной длины

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

Ответ 5

(на основе ответов @shenhengbin и @EchO на этот вопрос)

Предполагая, что вы заказываете по столбцу Col1:

ORDER BY LEN(Col1), Col1

Просто и элегантно.