比如下面一串字符，把连续的数字使用“-”连接起来。
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