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

SQL根据字符串中的数字排序，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