连续数字使用连接符替换，

连续数字使用连接符替换，

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

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 Source Code

 

另一个函数：

 

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 Source Code

 

最后实现如下：

 

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