连续数字使用连接符替换

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

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