基本上,我试图根据它的名字对表进行排序。表比较大,但为了示例,我只发布了一列。列在下面 0 E! {6 H1 b% R+ _+ wStation===ANTIL WELL 1ANTIL WELL 2BASELINE & CALIFORNIA WELLEPA WELL 6EPA WELL 7EPA WELL 108EPA WELL 109EPA WELL 110EPA WELL 111EPA WELL 112EPA WELL 108S通过尝试以下操作实现上述排序: $ l7 y5 L- @7 ]& Z/ U- a4 zorder 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之间移动,我尝试了很多不同的方法。6 I& J! {3 e0 h$ i1 J1 V
EPA之后,电台列表将继续。 / n5 y9 Z3 C. m- p8 v7 ` ( R' f5 X2 ]6 Q8 L& M. u% Y解决方案: 8 {0 N9 n9 `4 W9 T9 B9 ?
该解决方案比所选答案更可靠。如果站内有一个以上的数字(如 EPA WELL 5 " p; f* W+ p% Y4 K4 z6 h/ a7),答案可能无法提供预期答案。该解决方案将数字填充为‘0’,因此,所有数字都将被考虑为8位数。0 C& \# r% Z8 k
DECLARE @Table1 table([station] varchar(26))INSERT INTO @Table1 ([station])VALUES ANTIL WELL ANTIL WELL 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 stationFROM @table1ORDER BY CASE WHEN station not like '%[0-9]%' THEN station ELSE STUFF(station,PATINDEX('%[0-9]%',station),0,replicate PATINDEX('%[0-9]%',station) - len(station) PATINDEX('%[0-9]%',reverse(station)) 6))ENDGoatCD答案不会在我的测试数据中给出正确的顺序。