复杂的字段“查找-数字-字符串”。

Question

基本上，我试图按其名称对表进行排序。该表相对较大，但出于示例目的，我仅发布了一列。列在下方

Station
===
ANTIL WELL 1
ANTIL WELL 2
BASELINE & CALIFORNIA WELL
EPA WELL 6
EPA WELL 7
EPA WELL 108
EPA WELL 109
EPA WELL 110
EPA WELL 111
EPA WELL 112
EPA WELL 108S

上面的排序是通过尝试以下操作实现的：

order by left(station,LEN(station) -PATINDEX('%[^0-9]%',REVERSE(station))+1)
,CONVERT(int,REVERSE(LEFT(REVERSE(station), PATINDEX('%[^0-9]%',REVERSE(station)) - 1)))

但是，我无法对EPA WELL 108S进行分类。我需要它在EPA WELL 108和EPA WELL 109之间移动，我尝试了许多不同的方法。

EPA之后，电台列表也会继续。

Answer 1

该解决方案比所选答案更可靠。如果站中有超过1个数字（例如“ EPA WELL 5
7”），则此答案可能无法提供预期的答案。此解决方案将数字填充为‘0’，因此比较将考虑所有数字为8位数字。

DECLARE  @Table1 table([station] varchar(26))

INSERT INTO @Table1
    ([station])
VALUES
    ('ANTIL WELL 2'),
    ('ANTIL WELL 1'),
    ('BASELINE & CALIFORNIA WELL'),
    ('EPA WELL 7'),
    ('EPA WELL 6'),
    ('EPA WELL 108'),
    ('EPA WELL 109'),
    ('EPA WELL 110'),
    ('EPA WELL 111'),
    ('EPA WELL 112'),
    ('EPA WELL 108S'),
    ('EPA WELL 111108')
;

SELECT station
FROM @table1
ORDER BY 
CASE WHEN station not like '%[0-9]%' THEN station ELSE
   STUFF(station, PATINDEX('%[0-9]%',station), 0, replicate('0', 
   PATINDEX('%[0-9]%',station) - len(station) + PATINDEX('%[0-9]%',reverse(station)) + 6))
END

• GoatCD的答案不会在我的测试数据中给出正确的顺序。