SQL根据字符串中的数字排序

最新推荐文章于 2024-08-15 02:10:54 发布

原创最新推荐文章于 2024-08-15 02:10:54 发布 · 6.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #排序 #部分字符 #数字

sql server 专栏收录该内容

77 篇文章

订阅专栏

本文介绍了一种使用SQL对特定格式的过程名进行排序的方法，并进行了优化处理，确保即使过程名中缺少分隔符也能正确排序。

USE [Test]
GO

SELECT 
ProcessName
,INDEX1
,INDEX2
,INDEX3
,CAST(SUBSTRING(ProcessName,0,INDEX1) AS INT)*10000
+CAST(SUBSTRING(ProcessName,INDEX1+1,INDEX2-INDEX1-1) AS INT)*100
+CAST(SUBSTRING(ProcessName,INDEX2+1,INDEX3-INDEX2-1) AS INT)
AS Sort
FROM
(
	SELECT ProcessName
	,CHARINDEX('.',ProcessName) AS INDEX1
	,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName)+1) AS INDEX2
	,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName)+1)+1) AS INDEX3
	FROM ProcessPublish
) AS table1
ORDER BY Sort ASC

结果如下图：

优化代码：字符串中没有三个.，排序序号设置为0

USE [Test]
GO

SELECT 
ProcessName
,INDEX1
,INDEX2
,INDEX3
,CASE WHEN INDEX1>0 AND INDEX2>0 AND INDEX3>0 THEN--判断是否字符中包含3个.
	CAST(SUBSTRING(ProcessName,0,INDEX1) AS INT)*10000
	+CAST(SUBSTRING(ProcessName,INDEX1+1,INDEX2-INDEX1-1) AS INT)*100
	+CAST(SUBSTRING(ProcessName,INDEX2+1,INDEX3-INDEX2-1) AS INT)
ELSE 0 END
AS Sort
FROM
(
	SELECT ProcessName
	,CHARINDEX('.',ProcessName) AS INDEX1
	,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName)+1) AS INDEX2
	,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName,CHARINDEX('.',ProcessName)+1)+1) AS INDEX3
	FROM ProcessPublish
) AS table1
ORDER BY Sort ASC