基本上，我试图按其名称对表进行排序。该表相对较大，但出于示例目的，我仅发布了一列。列在下方
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), 0, replicate('0', PATINDEX('%[0-9]%',station) - len(station) + PATINDEX('%[0-9]%',reverse(station)) + 6)) END
