基本上,我试图按名称对表进行排序.该表相对较大,但为了示例,我只发布了一列.列位于下方
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的答案不会在我的测试数据中给出正确的顺序.
