我在我的表格中有以下数值：
ABC
ABC1
ABC2
ABC3 and so on...
ABC11
ABC12
ABC13 and so on..
ABC20
ABC21
ABC22 and so on..
基本上我手头有一个字符串值（不总是ABC，任何字符串值），它可能跟着一个数字，也可能是没有数字的字符串。
当我执行 select * from table order by my column 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
如何实现这一点？
(基于 @shenhengbin 和 @EchO 对 这个问题 的回答)
以下是我所谓的“干净的黑客”。假设您正在按列Col1排序：
ORDER BY LEN(Col1), Col1
这是一个hack，尽管我个人使用它会感到自豪。
select * from Test
order by CAST(SUBSTRING(Name + '0', PATINDEX('%[0-9]%', Name + '0'), LEN(Name + '0')) AS INT)
如果字符串中间有数字，则需要创建一个小型用户定义函数从字符串中获取数字，并根据该数字对数据进行排序，如下所示：
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
SELECT ColName
FROM TableName
ORDER BY
CASE WHEN ColName like '%[0-9]%'
THEN Replicate('0', 100 - Len(ColName)) + ColName
ELSE ColName END
int类型。SELECT Value,
Num=CAST(RIGHT(Value, LEN(Value) - 3) AS int)
FROM dbo.TableName
ORDER BY Num
WITH stringNumberData AS
( /* Build up Fake data with Numbers at the Beginning, End and Middle of the string */
SELECT 1 AS uniqueKey, 'ABC' AS NumberFromString UNION ALL
SELECT 2 AS uniqueKey, 'ABC1' AS NumberFromString UNION ALL
SELECT 3 AS uniqueKey, 'ABC2' AS NumberFromString UNION ALL
SELECT 4 AS uniqueKey, 'ABC3' AS NumberFromString UNION ALL
SELECT 5 AS uniqueKey, 'ABC10' AS NumberFromString UNION ALL
SELECT 6 AS uniqueKey, 'ABC11' AS NumberFromString UNION ALL
SELECT 7 AS uniqueKey, 'ABC12' AS NumberFromString UNION ALL
SELECT 8 AS uniqueKey, 'ABC20' AS NumberFromString UNION ALL
SELECT 9 AS uniqueKey, 'ABC21' AS NumberFromString UNION ALL
SELECT 10 AS uniqueKey, 'ABC22' AS NumberFromString UNION ALL
SELECT 11 AS uniqueKey, 'ABC30' AS NumberFromString UNION ALL
SELECT 12 AS uniqueKey, 'ABC31' AS NumberFromString UNION ALL
SELECT 13 AS uniqueKey, 'ABC32' AS NumberFromString UNION ALL
SELECT 14 AS uniqueKey, '1ABC' AS NumberFromString UNION ALL
SELECT 15 AS uniqueKey, '2ABC' AS NumberFromString UNION ALL
SELECT 16 AS uniqueKey, '3ABC' AS NumberFromString UNION ALL
SELECT 17 AS uniqueKey, '10ABC' AS NumberFromString UNION ALL
SELECT 18 AS uniqueKey, '11BC' AS NumberFromString UNION ALL
SELECT 19 AS uniqueKey, '12ABC' AS NumberFromString UNION ALL
SELECT 20 AS uniqueKey, '10ABC18' AS NumberFromString UNION ALL
SELECT 21 AS uniqueKey, '11BC52' AS NumberFromString UNION ALL
SELECT 22 AS uniqueKey, '12ABC42' AS NumberFromString UNION ALL
SELECT 23 AS uniqueKey, 'A3BC18' AS NumberFromString UNION ALL
SELECT 24 AS uniqueKey, 'B3C52' AS NumberFromString UNION ALL
SELECT 25 AS uniqueKey, '12AB3C' AS NumberFromString UNION ALL
SELECT 26 AS uniqueKey, 'A3BC' AS NumberFromString UNION ALL
SELECT 27 AS uniqueKey, 'AB2C' AS NumberFromString UNION ALL
SELECT 28 AS uniqueKey, 'ABC85D' AS NumberFromString
)
SELECT d.uniqueKey, d.NumberFromString
/* Extract numerical values contained on the LEFT of the String by finding the index of the first non number */
, LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS 'Left Numbers Extraction'
/* Extract numerical data contained on the RIGHT of the String */
, RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS 'Right Numbers Extraction'
/* The below checks inside the Inner string to determine if numbers exists within it. Could be used for further processing if further extraction is necessary */
, PATINDEX('%[0-9]%',
SUBSTRING(d.NumberFromString /*, Start Pos, Length to Extract) */
, PATINDEX('%[^0-9]%', d.NumberFromString) /* Start Pos is first left non number */
/* The below obtains the length of the Inner String so it can be extracted */
, LEN(d.NumberFromString) - ((PATINDEX('%[^0-9]%', d.NumberFromString) -1 )) - (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1) /* (String Length) - (LEFT Numbers) - (RIGHT Numbers) */
)) AS innerNumExists
/* The two lines below tell us if there exists a number at the Beginning and/or End of the string */
, PATINDEX('%[0-9]%', LEFT(d.NumberFromString, 1)) AS leftNumExists
, PATINDEX('%[0-9]%', RIGHT(d.NumberFromString, 1)) AS rightNumExists
/* Locates and returns the index of the very first number located in the string from Left to Right */
, PATINDEX('%[^0-9]%', d.NumberFromString) AS firstLeftNonNum_index
/* Locates and returns the index of the very first number located in the string from Right to Left */
, LEN(d.NumberFromString) - (PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 ) AS firstRightNonNum_index
/* Get the length of the numbers existing from Right to Left up to the first non numeric character */
, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) -1 AS rightStringLen
FROM stringNumberData d
ORDER BY
/* Ordering first by numbers found on the LEFT of the string */
CAST(LEFT(d.NumberFromString, PATINDEX('%[^0-9]%', d.NumberFromString) -1) AS INT )
/* Ordering second by numbers found on the RIGHT of the string */
, CAST(RIGHT(d.NumberFromString, PATINDEX('%[^0-9]%', REVERSE(d.NumberFromString)) - 1 ) AS INT )
;
这是一个简单易懂的示例，适用于使用SQL Server 17+的人。
DECLARE @Data table ( val varchar(10) );
INSERT INTO @Data VALUES
( 'ABC' ),( 'ABC1' ),( 'ABC11' ),( 'ABC12' ),( 'ABC13' ),( 'ABC2' ), ( 'B1C' ),
( 'ABC20' ),( 'ABC21' ),( 'ABC22' ),( 'ABC3' ),( 'ABC31' ),( 'ABC32' );
SELECT val FROM @Data AS d
CROSS APPLY (
SELECT CAST ( TRANSLATE ( d.val, 'abcdefghijklmnopqrstuvwxyz', ' ' ) AS int ) AS Num
) AS x
ORDER BY
LEFT ( val, 1 ), Num;
返回
+-------+
| val |
+-------+
| ABC |
| ABC1 |
| ABC2 |
| ABC3 |
| ABC11 |
| ABC12 |
| ABC13 |
| ABC20 |
| ABC21 |
| ABC22 |
| ABC31 |
| ABC32 |
| B1C |
+-------+
SQL Server的TRANSLATE有三个参数：inputString，characters，translations。
在您的情况下，inputString是您的列名。
characters是您要替换的值，例如字母表。
translations是要替换的值。此字符串的长度必须与characters相等，因此为空字符串，长度为26个空格。
最后，使用CAST忽略空格，并允许剩余值作为int进行排序。
您可以在此处阅读有关TRANSLATE的更多信息：
https://learn.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql?view=sql-server-ver15