连续数字使用连接符替换

比如下面一串字符，把连续的数字使用“-”连接起来。

DECLARE @Source NVARCHAR(MAX) = N'1,2,3,5,6,7,9,10,33,34,35,36,37,100,101,102,103,104,111,142,137,188,189,200,205,206'

结果要求如下：

 

解决问题，Insus.NET创建2个自定义函数：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:     Insus.NET
-- Create date: 2019-05-27
-- Update date: 2019-05-27
-- Description: 分割字符串
-- =============================================

CREATE FUNCTION [dbo].[tvf_SplitStringAsTwoField]
(
    @Source NVARCHAR(MAX)
)
RETURNS @returnResult TABLE
(
    [From] INT NOT NULL,
    [To] INT NOT NULL
)
AS
BEGIN   
    SET @Source = @Source + N',';

    IF CHARINDEX(',',@Source) > 0
    BEGIN
        WHILE CHARINDEX(',', @Source) > 0
        BEGIN
            DECLARE @CutoutString NVARCHAR(MAX) = SUBSTRING(@Source, 0, CHARINDEX(',', @Source))
            SET @Source = LTRIM(RTRIM(SUBSTRING(@Source, CHARINDEX(',', @Source) + 1, LEN(@Source))))

            DECLARE @from INT,@to INT
            SELECT TOP 1 @from = [From], @to = ([To]) FROM @returnResult ORDER BY [From] DESC
 
            IF @from IS NULL AND @to IS NULL        
                INSERT INTO @returnResult ([From],[To]) VALUES(@CutoutString,@CutoutString)        
            ELSE
            BEGIN
                IF @to + 1 = CAST(@CutoutString AS INT)           
                    UPDATE @returnResult SET [To] = @CutoutString WHERE [From] = @from            
                ELSE
                    INSERT INTO @returnResult ([From],[To]) VALUES(@CutoutString,@CutoutString)
            END    
        END
    END
    ELSE
        INSERT INTO @returnResult ([From],[To]) VALUES(@Source,@Source)
    RETURN
END
GO

 

另一个函数：

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-27
-- Update date: 2019-05-27
-- Description: 两个字段合并为一个
CREATE FUNCTION [dbo].[svf_TwoFieldMergedToOne] ( 
    @field1 NVARCHAR(MAX),
    @field2 NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
   DECLARE @rtv NVARCHAR(MAX) 

    IF LEN(ISNULL(@field1,N'')) > 0  AND LEN(ISNULL(@field2,N'')) > 0
    BEGIN
        IF @field1 = @field2
            SET @rtv = @field1
        ELSE
            SET @rtv = @field1 + N'-' + @field2            
    END        
    ELSE IF LEN(ISNULL(@field1,N'')) > 0  AND LEN(ISNULL(@field2,N'')) = 0
        SET @rtv = @field1 
    ELSE IF LEN(ISNULL(@field1,N'')) = 0  AND LEN(ISNULL(@field2,N'')) > 0
        SET @rtv = @field2 
    ELSE
        SET @rtv = N''

    RETURN  @rtv
END
GO

 

最后实现如下：

 

;WITH t AS
(
    SELECT [From],[To] FROM [dbo].[tvf_SplitStringAsTwoField](@Source)
)

SELECT [From],[To],[dbo].[svf_TwoFieldMergedToOne]([From],[To]) AS [Result]
FROM t

GO