转自：https://blog.csdn.net/qq_19299335/article/details/97784674

一、建表CodeOrder

DROP TABLE [dbo].[CodeOrder]
GO
CREATE TABLE [dbo].[CodeOrder] (
[code] varchar(255) NOT NULL 
)


GO


INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.1.4')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.10')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.2')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'2.1.3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'3')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'4')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'5.1.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.1')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.10')
GO
GO
INSERT INTO [dbo].[CodeOrder] ([code]) VALUES (N'1.2.2')
GO
GO

二、创建函数formatOrderId

CREATE FUNCTION [dbo].[formatOrderId](
@orderId VARCHAR(50),   --排序字段/需要格式化的数值，如column_name/1.1.1
@num  int,              --格式化后的数字每个段是几位数，默认4位
@separator VARCHAR(50)  --需要格式的的字段或者数值是以什么分隔符分隔的，默认为'.'
)
RETURNS  VARCHAR(50)
AS
BEGIN
    IF @num is NULL or @num = ''
    BEGIN
        SET @num=4               
    END
    IF @separator is NULL or @separator = ''
  BEGIN
        SET @separator='.'              
    END
    DECLARE @newOrderId VARCHAR(50)
  DECLARE @tempLen int
    SET @newOrderId=''
  set @tempLen=0
    WHILE LEN(@orderId)-LEN(REPLACE(@orderId,@separator,''))>0
    BEGIN
        set @tempLen=@num-LEN(LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1))
        IF @newOrderId=''
        BEGIN
            SET @newOrderId=REPLICATE('0', @tempLen) + LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1)   
        END
        ELSE
        BEGIN
            SET @newOrderId=@newOrderId+@separator+REPLICATE('0', @tempLen) + LEFT(@orderId,PATINDEX('%'+@separator+'%',@orderId)-1)
        END
        SET @orderId=RIGHT(@orderId,LEN(@orderId)-PATINDEX('%'+@separator+'%',@orderId))
    END
  set @tempLen=@num-LEN(@orderId)
  IF @newOrderId=''
    BEGIN
        SET @newOrderId=REPLICATE('0', @tempLen)+@orderId                 
    END
    ELSE
    BEGIN
        SET @newOrderId=@newOrderId+@separator+REPLICATE('0', @tempLen)+@orderId        
    END

    RETURN  @newOrderId
END

三、测试运行

select *,dbo.formatOrderId(code,5,'.') orderId from CodeOrder order  by orderId

结果：

select *,REPLACE(code, '.', '-') code2,dbo.formatOrderId(REPLACE(code, '.', '-'),5,'-') orderId from CodeOrder order  by orderId