USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
struct VARCHAR(50)
)
GO
INSERT INTO t
SELECT '1.1'
UNION ALL SELECT '1.2.1'
UNION ALL SELECT '1.10.1'
UNION ALL SELECT '1.10.2'
UNION ALL SELECT '1.10.10'
UNION ALL SELECT '1.1.1.1.1.1'
UNION ALL SELECT '1.3'
GO
---以上为测试表及测试数据
--增加函数
IF OBJECT_ID('dbo.Fun_AlignChar') IS NOT NULL DROP FUNCTION dbo.Fun_AlignChar
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-08-08
-- Description: 将聚合字符串补齐位数
-- =============================================
CREATE FUNCTION dbo.Fun_AlignChar
(
@str VARCHAR(200),@splitChar VARCHAR(20), @singleLen INT
)
RETURNS VARCHAR(400)
AS
BEGIN
DECLARE @r VARCHAR(400)
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @splitChar, ']]></x><x><![CDATA[') + ']]></x>')
SET @r = STUFF(
(SELECT '.'+REPLICATE('0',@singleLen-LEN(item))+item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
FOR XML PATH('')
),1,1,'')
RETURN @r;
END
GO
--查询
SELECT * FROM t
ORDER BY dbo.Fun_AlignChar(struct,'.',5)
/*
struct
1.1
1.1.1.1.1.1
1.2.1
1.3
1.10.1
1.10.2
1.10.10
*/