大佬教程收集整理的这篇文章主要介绍了Microsoft SQL Server中的自然(人类字母数字)排序,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
如何自然排序?
即.命令一组字母数字数据显示为:
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%的案例.但是,如果文本中有多个数值,则大多数/全部都不起作用,或者如果文本中根本没有找到数字,则会导致@L_944_15@错误.
我创建了这个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');
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 scheR_667_11845@ABINDING 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
以上是大佬教程为你收集整理的Microsoft SQL Server中的自然(人类字母数字)排序全部内容,希望文章能够帮你解决Microsoft SQL Server中的自然(人类字母数字)排序所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。