Microsoft SQL Server中的自然(人类字母数字)排序

发布时间：2020-12-14 02:01:55 所属栏目：Windows 来源：网络整理

导读：感谢您花时间阅读所有这些,很多！感谢所有爱好者！如何自然排序？即.命令一组字母数字数据显示为： Season 1,Season 2,Season 10,Season 20 代替 Season 1,Season 20 我以非常实用的格式使用一个非常实用的电视季节示例作为案例. 我希望完成以下任务：为

感谢您花时间阅读所有这些,很多！感谢所有爱好者！

如何自然排序？

即.命令一组字母数字数据显示为：

Season 1,Season 2,Season 10,Season 20

代替

Season 1,Season 20

我以非常实用的格式使用一个非常实用的电视季节示例作为案例.

我希望完成以下任务：

>为他人分享我的工作解决方案
>请求您的帮助,以确定如何缩短它(或找到更好的解决方案)到我的解决方案
>你能解决下面的标准7吗？

我花了大约2个小时在线研究,另外3个小时建立了这个解决方案.一些参考资料来自：

> SO Post
> MSDN
> Essential SQL
> Code Project
> DBA Stack Exchange

在SO和其他站点上找到的一些解决方案仅适用于90％的案例.但是,如果文本中有多个数值,则大多数/全部都不起作用,或者如果文本中根本没有找到数字,则会导致SQL错误.

我创建了这个SQLFiddle链接来玩(包括以下所有代码).

这是create语句：

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,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,reverse(title))) titleLeft,ltrim(right(title,PATINDEX('%[0-9] %',reverse(title)))) titleRight,reverse(title)))) titleRightTrim1,left(ltrim(right(title,reverse(title)))),PATINDEX('% %',reverse(title)))))) titleRightTrim2
          from
              tvseason) x) y
order by 
    titleLeft,titleRight

要考虑的标准：

>文字不包含数字
>文本包含开头和结尾的数字
>文本仅包含开头的数字
>文本仅包含末尾的数字
>文字最后可能包含(YYYY)
>文字可以以单个数字或双位数字结尾(例如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

解决方法

我认为这样做会有所帮助……我只是识别从非数字到数字的变化.
我没有进行任何大规模测试,但它应该相当快.

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)) 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,2)) ) s (str2)
            WHERE 
                t.n = 1
                OR SUBSTRING(@string,t.n - 1,2) LIKE '[0-9][^0-9]'
                OR SUBSTRING(@string,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,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,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,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,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!