Логическая сортировка строк в SQL с цифровыми частями, отсортированными как числовые значения
У меня есть таблица с такими кодами, как 11, 101, ST-1001, ST-100 и т. д., и я хочу отсортировать данные так, чтобы целочисленные части сортировались как их целочисленные значения. Но проблема в том, что она также связана с частями строк/varchar.
Итак, можно ли в MS-SQL сортировать коды «логически» так же, как функция Windows StrCmpLogicalW dll?
Я пробовал сортировать его с помощью оператора CASE, но думаю, что это станет слишком сложным, если придется учитывать все различные строковые префиксы. См. пример кода ниже.
CREATE TABLE metaforms (
Code varchar(10),
Description varchar(25),
Variables integer
);
GO
INSERT INTO metaforms (
Code,
Description,
Variables
) VALUES
('1', 'testform', 143),
('10015', 'measurement', 16),
('1020006510', 'Nutrition history', 50),
('1010010006', 'Translator', 8),
('10200310', 'Delivery Details: I', 13),
('10200320', 'Delivery Details: II', 12),
('10200330', 'Delivery Details: III', 12),
('1020100001', 'Comfort and Hygiene', 10),
('1020100025', 'Car Seat Evaluation', 7),
('1020100053', 'Length Weight', 7),
('AMB-100', 'AMB SF SA-DIABETES', 5),
('AMB-34', 'AMB SF SA-CHEST', 5),
('AMB-99', 'AMB SF SA-COUGH', 5),
('LQF-3912', 'DLBX-M DIAGNOSTICS (3912)', 21),
('LQF-40', 'DLBX-M DIAGNOSTICS (40)', 24),
('LQF-588', 'DLBX-M DIAGNOSTICS (588)', 34),
('ST-1010', 'Standard form 1010', 9),
('ST-200', 'Standard form 200', 9),
('ST-35', 'Standard form 35', 9);
-- try sorting
select * from metaforms
order by case when isnumeric(code)=1 then right('000000000000'+code, 12) else code end;
select * from metaforms order by LEN(code), code;
select * from metaforms
order by case when isnumeric(code)=1 then LEN(code) end, code;
Операторы select в приведенном выше коде правильно сортируют числовые значения, но не коды, которые также имеют строковую часть.
Таким образом, желаемый отсортированный результат должен быть таким:
Code
-----------
1
10015
10200310
10200320
10200330
1010010006
1020006510
1020100001
1020100025
1020100053
AMB-34
AMB-99
AMB-100
LQF-40
LQF-588
LQF-3912
ST-35
ST-200
ST-1010
Шаблон в данных вашего примера всегда содержит числа, которым иногда предшествуют строка и тире. Так что вы можете повозиться с charindex('-') и отсортировать обе части по отдельности. Конечно, для повседневного использования вы можете предпочесть хранить части в таблице отдельно или добавлять ведущие нули, если это возможно.
Функция SQLCLR, использующая StrCmpLogicalW напрямую, может быть наиболее точной, хотя и медленной. И именно по этой причине не следует хранить несколько фрагментов информации в одном столбце.
Ответы 1
Если все ваши данные действительно соответствуют показанному шаблону ^([A-Z]*-|)[0-9]+$, мы можем воспользоваться -, также обозначающим отрицательные числа. Поэтому вместо того, чтобы возиться с такими вещами, как «charindex + 1», мы просто берем абсолютное значение.
select *
from #metaforms
order by left(code, charindex('-', Code))
, abs(substring(code, charindex('-', Code), 999999999))
В качестве альтернативы вы можете сделать это. Наверное, действительно лучше?
select *
from #metaforms
order by left(code, charindex('-', Code))
, cast(right(code, len(code) - charindex('-', Code)) as int)
Выход:
1
10015
10200310
10200320
10200330
1010010006
1020006510
1020100001
1020100025
1020100053
AMB-34
AMB-99
AMB-100
LQF-40
LQF-588
LQF-3912
ST-35
ST-200
ST-1010
Спасибо, я думаю, что это лучший ответ без необходимости добавления пользовательских функций. Я хотел вызвать функцию StrCmpLogicalW, выполнив create assembly shlwapi from 'C:\Windows\System32\shlwapi.dll', но, видимо, shlwapi.dll не является чистой сборкой .NET, поэтому не управляется с помощью CLR, поэтому его нельзя использовать в MSSQL.
Лучшим вариантом здесь может быть сохранение текстового префикса и числового суффикса в отдельных столбцах. Запрос того, что вы хотите, будет уродливым и непроизводительным.