22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE tb (ID INT IDENTITY(1,1),Code VARCHAR(20))
GO
INSERT tb SELECT '1'
UNION ALL SELECT '2'
UNION ALL SELECT '3'
UNION ALL SELECT '1.1'
UNION ALL SELECT '2.1'
UNION ALL SELECT '131.2.1.11.1.131111'
UNION ALL SELECT '2.2'
UNION ALL SELECT '121.2.1.1.1.131111'
UNION ALL SELECT '3.1'
UNION ALL SELECT '3.2'
UNION ALL SELECT '20.1'
UNION ALL SELECT '1.1.1'
UNION ALL SELECT '1.1.2'
UNION ALL SELECT '1.1.3'
UNION ALL SELECT '1.1.11.1'
UNION ALL SELECT '1.1.2.1'
UNION ALL SELECT '2.1.2.1'
GO
CREATE FUNCTION dbo.ReplaceCode(@code VARCHAR(20))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @re VARCHAR(8000),@tempStr VARCHAR(100)
SELECT @re='',@tempStr=''
WHILE CHARINDEX('.',@code)>0
BEGIN
SELECT @tempStr=LEFT(@code,CHARINDEX('.',@code)-1),
@re=@re+RIGHT('0000000000'+@tempStr,10),
@code=STUFF(@code,1,CHARINDEX('.',@code),'')
END
IF LEN(@code)>0
SET @re=@re+RIGHT('0000000000'+@code,10)
RETURN @re
END
GO
SELECT *
FROM tb
ORDER BY dbo.ReplaceCode(code)
DROP TABLE tb
DROP FUNCTION ReplaceCode