复杂的字段“字符串-数字-字符串"字段 [英] Complex sort of field "string - number - string"
问题描述
基本上,我正在尝试按名称对表进行排序.该表相对较大,但出于示例目的,我仅发布了一列.列位于下方的站
Basically, I am trying to sort a table by its name. The table is relatively big, but I am posting only one column, for sake of example. The Column is Station below
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
上面的排序是通过我尝试以下操作实现的:
The sort above was achieved by me trying this:
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之间移动,我尝试了许多不同的方法.
However, I just can't sort that EPA WELL 108S. I need it to go between EPA WELL 108 and EPA WELL 109, I tried many many different ways.
在EPA之后,电台的名单也会继续.
Also the list of the stations goes on after EPA.
推荐答案
此解决方案比所选答案更可靠.如果站点中有超过1个数字(例如"EPA WELL 5 7"),则此答案可能无法提供预期的答案.此解决方案是将数字填充为'0',以便比较时将所有数字都考虑为8位数字.
This solution is more reliable than the chosen answer. This answer may not give the expected answer if there is more than 1 number like 'EPA WELL 5 7' in station. This solution is padding the number with '0's so the comparison will consider all numbers 8 digits.
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的答案不会在我的测试数据中给出正确的顺序.
*GoatCD's answer will not give the correct order in my test data.
这篇关于复杂的字段“字符串-数字-字符串"字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!