代码之家 › 专栏 › 技术社区 › sai bharath

按数值显示字母数字列顺序

sql-order-by sql-server-2008-r2 sql-server-2008 sql-server

-1

sai bharath · 技术社区 · 7 年前

BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 12(PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)

我想要这样的数据:-

BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
COMPRESSION 12(PB4)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)

当你比较两者时,你可以看到 Compression 12 .如何编写查询以获得所需的输出?

1 回复 | 直到 7 年前

Jason A. Long 7 年前

这是可以做到的,但你必须解析出这些数字,并将其转换为INT,然后才能将它们作为数字排序。这里有几个不同的例子。我不会说这两者都过于优雅,但除了向表中添加索引排序列之外,真的不会有这样的优雅。

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
    SomeString VARCHAR(100)
    );
INSERT #TestData(SomeString) VALUES
    ('BQS 1 (CP)'),
    ('BQS 2 (CP)'),
    ('BQS 3 (CP)'),
    ('BQS 5 (PB1)'),
    ('CAPSULE FILLING 3 ZRO (PB4)'),
    ('CAPSULE FILLING 6 A150T(PB4)'),
    ('COMPRESSION 1 (PB4)'),
    ('COMPRESSION 3 (PB4)'),
    ('COMPRESSION 4 (PB4 EX)'),
    ('COMPRESSION 5 (PB4 EX)'),
    ('COMPRESSION 12(PB4)'),
    ('PHARMA PACK 1 (CP)'),
    ('PHARMA PACK 2 (CP)'),
    ('PP III (CP)');

--====================================================================

-- option 1...
SELECT
    Option_1 = td.SomeString
FROM
    #TestData td
    CROSS APPLY ( VALUES (NULLIF(PATINDEX('%[0-9]%', td.SomeString), 0)) ) x1 (N)
    CROSS APPLY ( VALUES (NULLIF(CHARINDEX('(', REPLACE(td.SomeString, ' ', '('), x1.N), 0)) ) x2 (P)
    CROSS APPLY ( VALUES (LEFT(td.SomeString, ISNULL(x1.N - 2, 100))) ) s1 (Sort1)
    CROSS APPLY ( VALUES (TRY_CAST(SUBSTRING(td.SomeString, ISNULL(x1.N, 1), ISNULL(x2.P - x1.N, 100)) AS INT)) ) s2 (Sort2)
ORDER BY
    s1.Sort1,
    s2.Sort2;

-- option 2...
SELECT 
    OPTION_2 = td.SomeString
FROM
    #TestData td
    CROSS APPLY ( VALUES (NULLIF(PATINDEX('%[0-9]%', td.SomeString), 0)) ) x (N)
    CROSS APPLY ( VALUES (LEFT(td.SomeString, ISNULL(x.N - 2, 100))) ) s1 (Sort1)
    CROSS APPLY ( VALUES (CASE 
                            WHEN SUBSTRING(td.SomeString, x.N, 5) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 5) AS INT) 
                            WHEN SUBSTRING(td.SomeString, x.N, 4) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 4) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 3) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 3) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 2) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 2) AS INT)
                            WHEN SUBSTRING(td.SomeString, x.N, 1) NOT LIKE '%[^0-9]%' THEN CAST(SUBSTRING(td.SomeString, x.N, 1) AS INT)
                        END) ) s2 (Sort2)
ORDER BY
    s1.Sort1,
    s2.Sort2;

Option_1
----------------------------------------
BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
COMPRESSION 12(PB4)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)

OPTION_2
-----------------------------------------
BQS 1 (CP)
BQS 2 (CP)
BQS 3 (CP)
BQS 5 (PB1)
CAPSULE FILLING 3 ZRO (PB4)
CAPSULE FILLING 6 A150T(PB4)
COMPRESSION 1 (PB4)
COMPRESSION 3 (PB4)
COMPRESSION 4 (PB4 EX)
COMPRESSION 5 (PB4 EX)
COMPRESSION 12(PB4)
PHARMA PACK 1 (CP)
PHARMA PACK 2 (CP)
PP III (CP)