sql – 复杂的字段“string – number – string”

基本上,我试图按名称对表进行排序.该表相对较大,但为了示例,我只发布了一列.列位于下方

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之后,电台列表也在继续.

解决方法

该解决方案比所选答案更可靠.如果站内有超过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),replicate('0',station) - len(station) + PATINDEX('%[0-9]%',reverse(station)) + 6))
END

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