排序包含整数的VARCHAR列

Question

我有这个表:

IF OBJECT_ID('tempdb..#Test') IS NOT NULL 
DROP TABLE #Test;
CREATE TABLE #Test (Col VARCHAR(100));
INSERT INTO #Test 
VALUES ('1'), ('2'), ('10'), ('A'), ('B'), ('C1'), ('1D'), ('10HH')
SELECT * FROM #Test

我想先按数值排序，然后按字母顺序排序。

我想排序的结果是:

1
1D
2
10
10HH
A
B
C1

假设条目的结构是其中之一(当然没有破折号)

number
number-string
string-number
string

如果存在类似string-number-string的条目，则假定它是string-number

Answer 1

这个不好看，但是很好用。

SELECT T.Col
FROM #Test T
CROSS APPLY (VALUES(PATINDEX('%[^0-9]%',T.Col)))PI(I)
CROSS APPLY (VALUES(TRY_CONVERT(int,NULLIF(ISNULL(LEFT(T.Col,NULLIF(PI.I,0)-1),LEN(T.Col)),''))))TC(L)
ORDER BY CASE WHEN TC.L IS NULL THEN 1 ELSE 0 END,
TC.L,
T.Col;

老实说，我建议，如果你想排序你的数据像一个数值，你实际上存储数值列的数值;显然，上面应该是一个数字前缀值，然后是字符串后缀。如果您希望拥有您所拥有的值，那么可以使用(PERSISTED)计算列。这样的:

CREATE TABLE #Test (Prefix int NULL,
Suffix varchar(100) NULL,
Col AS CONCAT(Prefix, Suffix) PERSISTED);
INSERT INTO #Test (Prefix, Suffix)
VALUES (1,NULL), (2,NULL), (10,NULL), (NULL,'A'), (NULL,'B'), (NULL,'C1'), (1,'D'), (10,'HH');
SELECT Col
FROM #Test
ORDER BY CASE WHEN Prefix IS NULL THEN 1 ELSE 0 END,
Prefix,
Suffix;

Answer 2

这是一个糟糕且不直观的解决方案，如果您将两部分数据分开存储，则没有必要，这是由bad idea designs™带来的:

;WITH cte AS 
(
SELECT Col, rest = SUBSTRING(Col, pos, 100),
possible_int = TRY_CONVERT(bigint, CASE WHEN pos <> 1 THEN 
LEFT(Col, COALESCE(NULLIF(pos,0),100)-1) END)
FROM (SELECT Col, pos = PATINDEX('%[^0-9]%', Col) FROM #Test) AS src
)
SELECT Col FROM cte
ORDER BY CASE 
WHEN possible_int IS NULL THEN 2 ELSE 1 END, 
possible_int, 
rest;

结果:

坳1 dC1