时间戳

首页 > 解决方案 > 对包含整数的 VARCHAR 列进行排序

sql - 对包含整数的 VARCHAR 列进行排序

问题描述

我有这张桌子：

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

标签： sqlsql-servertsql

解决方案

它不漂亮，但它有效。

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;

推荐阅读