Естественная (человеческая буквенно-цифровая) сортировка в Microsoft SQL Server
Спасибо, что нашли время, чтобы прочитать все это, это много! Цените всех вас, друзья-энтузиасты!
Как натуральная сортировка?
то есть. упорядочить набор буквенно-цифровых данных как:
Season 1, Season 2, Season 10, Season 20
вместо
Season 1, Season 10, Season 2, Season 20
Я использую очень практический пример телевизионных сезонов в очень практичном формате.
Я хочу сделать следующее:
- Поделитесь своим рабочим решением для других
- Спросите свою помощь в определении, как сократить его (или найти лучшее решение) для моего решения
- Можете ли вы решить критерии 7 ниже?
Я потратил около 2 часов на исследования в Интернете и еще 3 часа на разработку этого решения. Некоторые из справочных материалов взяты из:
Некоторые из решений, найденных на SO и других сайтах, работают только в 90% случаев. Однако большинство / все НЕ работает, если в вашем тексте несколько числовых значений, или вызовет ошибку SQL, если в тексте вообще не будет найдено ни одного числа.
Я создал эту ссылку SQLFiddle, чтобы поиграться (включает в себя весь код ниже).
Вот инструкция создания:
create table tvseason
(
title varchar(100)
);
insert into tvseason (title)
values ('100 Season 03'), ('100 Season 1'),
('100 Season 10'), ('100 Season 2'),
('100 Season 4'), ('Show Season 1 (2008)'),
('Show Season 2 (2008)'), ('Show Season 10 (2008)'),
('Another Season 01'), ('Another Season 02'),
('Another 1st Anniversary Season 01'),
('Another 2nd Anniversary Season 01'),
('Another 10th Anniversary Season 01'),
('Some Show Another No Season Number'),
('Some Show No Season Number'),
('Show 2 Season 1'),
('Some Show With Season Number 1'),
('Some Show With Season Number 2'),
('Some Show With Season Number 10');
Вот мое рабочее решение (только не в состоянии решить критерии № 7 ниже):
select
title, "index", titleLeft,
convert(int, coalesce(nullif(titleRightTrim2, ''), titleRight)) titleRight
from
(select
title, "index", titleLeft, titleRight, titleRightTrim1,
case
when PATINDEX('%[^0-9]%', titleRightTrim2) = 0
then titleRightTrim2
else left(titleRightTrim2, PATINDEX('%[^0-9]%', titleRightTrim2) - 1)
end as titleRightTrim2
from
(select
title,
len(title) - PATINDEX('%[0-9] %', reverse(title)) 'index',
left(title, len(title) - PATINDEX('%[0-9] %', reverse(title))) titleLeft,
ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRight,
ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))) titleRightTrim1,
left(ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))), PATINDEX('% %', ltrim(right(title, PATINDEX('%[0-9] %', reverse(title)))))) titleRightTrim2
from
tvseason) x) y
order by
titleLeft, titleRight
Критерии для рассмотрения:
- Текст не содержит цифр
- Текст содержит числа в начале и в конце
- Текст содержит цифры только в начале
- Текст содержит только цифры в конце
- Текст может содержать (ГГГГ) в конце
- Текст может заканчиваться одной цифрой ИЛИ двойной цифрой (например, 1 или 01)
- Необязательно: любая комбинация выше, плюс цифры в середине текста
Вот вывод:
title
100 Season 1
100 Season 2
100 Season 03
100 Season 4
100 Season 10
**Case 7 here**
Another 10th Anniversary Season 01
Another 1st Anniversary Season 01
Another 2nd Anniversary Season 01
Another Season 01
Another Season 02
Show (2008) Season 1
Show (2008) Season 2
Show 2 The 75th Anniversary Season 1
Show Season 1 (2008)
Show Season 2 (2008)
Show Season 10 (2008)
Some Show Another No Season Number
Some Show No Season Number
Some Show With Season Number 1
Some Show With Season Number 2
Some Show With Season Number 10
3 ответа
Я думаю, что это поможет... Я просто распознаю изменения от нечисловых к числовым. Я не проводил масштабных испытаний, но это должно быть достаточно быстро.
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO
ALTER FUNCTION dbo.tfn_SplitForSort
/* ===================================================================
11/11/2018 JL, Created: Comments
=================================================================== */
--===== Define I/O parameters
(
@string VARCHAR(8000)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
cte_Tally (n) AS (
SELECT TOP (LEN(@string))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
cte_n2 a CROSS JOIN cte_n2 b
),
cte_split_string AS (
SELECT
col_num = ROW_NUMBER() OVER (ORDER BY t.n) + CASE WHEN LEFT(@string, 1) LIKE '[0-9]' THEN 0 ELSE 1 END,
string_part = SUBSTRING(@string, t.n, LEAD(t.n, 1, 8000) OVER (ORDER BY t.n) - t.n)
FROM
cte_Tally t
CROSS APPLY ( VALUES (SUBSTRING(@string, t.n, 2)) ) s (str2)
WHERE
t.n = 1
OR SUBSTRING(@string, t.n - 1, 2) LIKE '[0-9][^0-9]'
OR SUBSTRING(@string, t.n - 1, 2) LIKE '[^0-9][0-9]'
)
SELECT
so_01 = ISNULL(MAX(CASE WHEN ss.col_num = 1 THEN CONVERT(FLOAT, ss.string_part) END), 99999999),
so_02 = MAX(CASE WHEN ss.col_num = 2 THEN ss.string_part END),
so_03 = MAX(CASE WHEN ss.col_num = 3 THEN CONVERT(FLOAT, ss.string_part) END),
so_04 = MAX(CASE WHEN ss.col_num = 4 THEN ss.string_part END),
so_05 = MAX(CASE WHEN ss.col_num = 5 THEN CONVERT(FLOAT, ss.string_part) END),
so_06 = MAX(CASE WHEN ss.col_num = 6 THEN ss.string_part END),
so_07 = MAX(CASE WHEN ss.col_num = 7 THEN CONVERT(FLOAT, ss.string_part) END),
so_08 = MAX(CASE WHEN ss.col_num = 8 THEN ss.string_part END),
so_09 = MAX(CASE WHEN ss.col_num = 9 THEN CONVERT(FLOAT, ss.string_part) END),
so_10 = MAX(CASE WHEN ss.col_num = 10 THEN ss.string_part END)
FROM
cte_split_string ss;
GO
Функция используется...
SELECT
ts.*
FROM
#tvseason ts
CROSS APPLY dbo.tfn_SplitForSort (ts.title) sfs
ORDER BY
sfs.so_01,
sfs.so_02,
sfs.so_03,
sfs.so_04,
sfs.so_05,
sfs.so_06,
sfs.so_07,
sfs.so_08,
sfs.so_09,
sfs.so_10;
Результаты:
id title
----------- ------------------------------------------
2 100 Season 1
4 100 Season 2
1 100 Season 03
5 100 Season 4
3 100 Season 10
11 Another 1st Anniversary Season 01
12 Another 2nd Anniversary Season 01
13 Another 10th Anniversary Season 01
9 Another Season 01
10 Another Season 02
16 Show 2 Season 1
6 Show Season 1 (2008)
7 Show Season 2 (2008)
8 Show Season 10 (2008)
14 Some Show Another No Season Number
15 Some Show No Season Number
17 Some Show With Season Number 1
18 Some Show With Season Number 2
19 Some Show With Season Number 10
Это требование вопроса является сложным. Так что это не может быть достигнуто простым запросом. Итак, мое решение ниже: сначала я создаю пример данных, которые будут использоваться в этом запросе.
CREATE TABLE #TVSEASON (TITLE VARCHAR(100));
INSERT INTO #TVSEASON (TITLE) VALUES
('100'),
('100 SEASON 03'),
('100 SEASON 1'),
('100 SEASON 10'),
('100 SEASON 2'),
('100 SEASON 4'),
('SHOW (2008) SEASON 1'),
('SHOW (2008) SEASON 2'),
('SHOW SEASON 1 (2008)'),
('SHOW SEASON 2 (2008)'),
('SHOW SEASON 10 (2008)'),
('ANOTHER 1ST ANNIVERSARY SEASON 01'),
('ANOTHER 2ND ANNIVERSARY SEASON 01'),
('ANOTHER 10TH ANNIVERSARY SEASON 01'),
('ANOTHER SEASON 01'),
('ANOTHER SEASON 02'),
('SOME SHOW ANOTHER NO SEASON NUMBER'),
('SOME SHOW NO SEASON NUMBER'),
('SHOW 2 THE 75TH ANNIVERSARY SEASON 1'),
('SOME SHOW WITH SEASON NUMBER 1'),
('SOME SHOW WITH SEASON NUMBER 2'),
('SOME SHOW WITH SEASON NUMBER 10')
Для достижения желаемого результата я создаю функцию для разделения всех слов и цифр из текста. (Примечание: я также удаляю st из 1st, nd из 2nd и т. Д. С помощью функции после обрезки пробелов между 1-м для безопасной стороны, если любой пользователь ошибочно вводит пробелы между 1-м, поэтому, если вы считаете, что вероятность ошибки отсутствует, вы удаляете LTRIM из эта функция, потому что для удаления этих значений также удаляется th, если текст имеет значение типа "1 the title", которое будет конвертировано в 1 e title)
--CREATE SPLIT FUNCTION
CREATE FUNCTION [dbo].[SplitAlphaNumeric]
(
@LIST NVARCHAR(2000)
)
RETURNS @RTNVALUE TABLE
(
ID INT IDENTITY(1,1),
WORDS NVARCHAR(100),
NUMBERS INT
)
AS
BEGIN
WHILE (PATINDEX('%[0-9]%',@LIST) > 0)
BEGIN
INSERT INTO @RTNVALUE (WORDS, NUMBERS)
SELECT CASE WHEN PATINDEX('%[0-9]%',@LIST) = 0 THEN @LIST
WHEN (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN ''
WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN ''
ELSE SUBSTRING(@LIST, 1, PATINDEX('%[0-9]%',@LIST) - 1)
END,
CASE WHEN PATINDEX('%[0-9]%',@LIST) = 0 THEN NULL
WHEN (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN CAST(LTRIM(RTRIM(@LIST)) AS INT)
WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN SUBSTRING(@LIST, 1, PATINDEX('%[^0-9]%',@LIST) - 1)
ELSE NULL
END
SET @LIST = LTRIM(RTRIM(CASE WHEN PATINDEX('%[0-9]%',@LIST) = 0 OR (PATINDEX('%[0-9]%',@LIST) = 1 AND PATINDEX('%[^0-9]%',@LIST) = 0) THEN ''
WHEN PATINDEX('%[0-9]%',@LIST) = 1 THEN
CASE WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'ST%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'ND%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'RD%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
WHEN LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))) LIKE 'TH%' THEN SUBSTRING(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST)))),3, LEN(LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))))
ELSE LTRIM(SUBSTRING(@LIST, PATINDEX('%[^0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[^0-9]%',REVERSE(@LIST))))
END
ELSE SUBSTRING(@LIST, PATINDEX('%[0-9]%',@LIST), LEN(@LIST)-PATINDEX('%[0-9]%',REVERSE(@LIST)))
END))
END
INSERT INTO @RTNVALUE (WORDS)
SELECT VALUE = LTRIM(RTRIM(@LIST))
RETURN
END
На третьем шаге я использую cross apply при вызове функции, потому что функция возвращает таблицу для заданного строкового значения. По запросу select я вставляю все столбцы во временную таблицу для сортировки значений в соответствии с требованием на следующем шаге.
SELECT T.TITLE, A.ID, A.NUMBERS, A.WORDS INTO #FINAL
FROM #TVSEASON T
CROSS APPLY dbo.SplitAlphaNumeric(TITLE) A
Из временной таблицы #Final я использую вещи для конкатенации всех слов, чтобы снова создать заголовок без какого-либо числа в тексте, а затем использую эти значения для упорядочивания заголовка.
Вы можете изменить этот запрос для порядка в любой последовательности, например, если вы хотите упорядочить по тексту, тогда вы заказываете первый текстовый столбец, а затем числа, но если вы хотите упорядочить по суммированию всех чисел, которые используются в заголовке, то порядковые номера сначала после сумма, как я, или, если вы хотите заказать по простому номеру без суммы, то не используйте группирование по предложению и подзапросу и напрямую упорядочивать по числам. Короче говоря, вы можете получить все последовательности, соответствующие буквенно-цифровым значениям, после изменения запроса ниже, а верхний является базовым запросом для всех целей.
SELECT A.TITLE--, A.NUMBERS, A.TEXTVAL
FROM (
SELECT A.TITLE,
STUFF((
SELECT ' ' + B.WORDS
FROM #FINAL B
WHERE B.TITLE = A.TITLE
FOR XML PATH(''),TYPE).VALUE('(./TEXT())[1]','VARCHAR(MAX)')
,1,1,'') TEXTVAL,
SUM(ISNULL(A.NUMBERS,0)) NUMBERS
FROM #FINAL A
GROUP BY A.TITLE
) A
ORDER BY A.TEXTVAL, A.NUMBERS
DROP TABLE #FINAL
DROP TABLE #TVSEASON
В последнем я удаляю обе временные таблицы из памяти. Я думаю, что это запрос на сортировку значений, который вы хотите, потому что, если у кого-то есть другое требование к порядку, снова алфавитно-цифровые значения, они могут выполнить свое требование после небольшого изменения этого запроса.
Лично я бы старался избегать сложных манипуляций со строками в SQL. Я, вероятно, выгрузил бы его в текстовый файл и обработал бы его, используя регулярное выражение в чем-то вроде C# или Python. Затем запишите его обратно в БД в отдельном столбце. Общеизвестно, что SQL плохо работает со строками.
Однако вот мой удар по подходу SQL. Идея в основном состоит в том, чтобы сначала исключить все строки, которые не имеют строки Season [number] в них. Это относится к случаю, когда нет сезонов для разбора. Я решил включить их с пустыми значениями, но вы можете с легкостью опустить их в предложении where или указать для них значение по умолчанию. Я использую stuff() функция отрезать все до строки Season [number]так с ним легче работать.
Теперь у нас есть строка, начинающаяся с номера сезона и потенциально заканчивающаяся мусором. Я использую оператор case, чтобы увидеть, есть ли мусор (что-либо не числовое), и если есть, я беру самые левые числовые символы и выбрасываю остальные. Если для начала есть только числовое значение, я просто оставляю его как есть.
Наконец, приведите его как int и сортируйте по нему.
if object_id('tempdb.dbo.#titles') is not null drop table #titles
create table #titles (Title varchar(100))
insert into #titles (TItle)
select title = '100 Season 1'
union all select '100 Season 2'
union all select '100 Season 03'
union all select '100 Season 4'
union all select '100 Season 10'
union all select 'Another 10th Anniversary Season 01'
union all select 'Another 1st Anniversary Season 01'
union all select 'Another 2nd Anniversary Season 01'
union all select 'Another Season 01'
union all select 'Another Season 02'
union all select 'Show (2008) Season 1'
union all select 'Show (2008) Season 2'
union all select 'Show 2 The 75th Anniversary Season 1'
union all select 'Show Season 1 (2008)'
union all select 'Show Season 2 (2008)'
union all select 'Show Season 10 (2008)'
union all select 'Some Show Another No Season Number'
union all select 'Some Show No Season Number'
union all select 'Some Show With Season Number 1'
union all select 'Some Show With Season Number 2'
union all select 'Some Show With Season Number 10'
;with src as
(
select
Title,
Trimmed = case when Title like '%Season [0-9]%'
then stuff(title, 1, patindex('%season [0-9]%', title) + 6, '')
else null
end
from #titles
)
select
Season = cast(case when Trimmed like '%[^0-9]%' then left(Trimmed, patindex('%[^0-9]%', Trimmed))
else Trimmed
end as int),
Title
from src
order by Season
В моем ответе используется OPEN_JSON, чтобы разбить каждый заголовок на слова, а затем он заменяет числа на такое же количество "а". например, 2 становится аа, а 10 становится аааааааа. У нас остается набор строк, по 1 на каждое слово. Затем я снова объединяю их вместе, используя STRING_AGG в каждом заголовке, чтобы создать новый заголовок, содержащий числа, замененные на. Затем я сортирую по этому и сообщаю исходное название:
with Words1 as
(
select title, REPLACE(REPLACE(value, '(', ''), ')', '') word, [key] as RowN
from tvseason
CROSS APPLY OPENJSON('["' +
REPLACE(REPLACE(REPLACE(title,' ','","'),'\','\\"'),'"','\"') +
'"]')
),
Words2
AS
(
SELECT title,
CASE
WHEN ISNUMERIC(word) = 1 THEN Replicate('a', CAST(Word as INT))
WHEN word like '%st' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
WHEN word like '%nd' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
WHEN word like '%rd' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
WHEN word like '%th' AND ISNUMERIC(LEFT(word, LEN(Word)-2)) = 1
THEN Replicate('a', CAST(LEFT(Word, LEN(Word)-2) as INT))
else Word
END As Word,
rowN
from words1
),
Words3
AS
(
SELECT title, STRING_AGG(Word, ' ') WITHIN GROUP (Order By rowN ASC) AS TitleLong
FROM Words2
GROUP BY Title
)
SELECT title
FROM Words3
ORDER BY TitleLong
Это дает следующие результаты:
**title**
100 Season 1
100 Season 2
100 Season 03
100 Season 4
100 Season 10
Another 1st Anniversary Season 01
Another 2nd Anniversary Season 01
Another 10th Anniversary Season 01
Another Season 01
Another Season 02
Show 2 Season 1
Show Season 1 (2008)
Show Season 2 (2008)
Show Season 10 (2008)
Some Show Another No Season Number
Some Show No Season Number
Some Show With Season Number 1
Some Show With Season Number 2
Some Show With Season Number 10