展开中断或忽略的序号，

展开中断或忽略的序号，

网上有人问：

declare @WH varchar(MAX)
set @WH='C1,C2,C3-C9,C20,C22,C30-C35'

想要循环截取出含有-的字符串 C3-C9，C30-C35，展开C3-C9，C30-C35，
将@WH替换为 'C1,C2,C3,C4,C5,C6,C7,C8,C9,C20,C22,C30,C31,C32,C33,C34,C35'

Insus.NET也想练习一下。

写一个自定义函数来处理需要展开的字符串：

 

 

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_FillConnectionString] ( @string NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN DECLARE @rtv NVARCHAR(MAX) = N'' DECLARE @hyphen VARCHAR(1) = N'-' IF @string LIKE '%'+ @hyphen +'%' BEGIN DECLARE @hyphen_position INT = CHARINDEX(@hyphen, @string) DECLARE @Front NVARCHAR(40) = SUBSTRING(@string, 1, @hyphen_position - 1) DECLARE @Back NVARCHAR(40) = SUBSTRING(@string, @hyphen_position + 1, LEN(@string)- @hyphen_position) DECLARE @f INT = CAST(SUBSTRING(@Front, 2, LEN(@Front) - 1) AS INT) DECLARE @b INT = CAST(SUBSTRING(@Back, 2, LEN(@Back) - 1) AS INT) WHILE @f <= @b BEGIN SET @rtv = @rtv + N',' + N'C' + CAST(@f AS NVARCHAR(MAX)) SET @f = @f + 1 END END RETURN CASE WHEN LEN(ISNULL(@rtv,N'')) = 0 THEN @string ELSE STUFF(@rtv,1,1,N'') END END 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].[tvf_ConvertStringToTable] ( @OriginalString NVARCHAR(MAX), @Delimiter CHAR(1) ) RETURNS @Result TABLE ([value] NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML = CAST('<insus>' + REPLACE(@OriginalString,@Delimiter,'</insus><insus>') + '</insus>' AS XML) INSERT INTO @Result([value]) SELECT n.value('.','NVARCHAR(MAX)') AS w FROM @xml.nodes('/insus') AS E(n) RETURN END GO Source Code

 

现在试使用上面2个函数，看看结果如何？

 

DECLARE @WH VARCHAR(MAX) SET @WH='C1,C2,C3-C9,C20,C22,C30-C35' ;WITH HW AS ( SELECT [value] FROM [dbo].[tvf_ConvertStringToTable](@WH,',') ) SELECT [value],[dbo].[svf_FillConnectionString]([value]) as [str] FROM HW Source Code

差不多接近要求了，现在只要把[str]这列的数据连接起来就OK了。

如何连接，参考这篇《列值转换为逗号分隔字符串》https://www.cnblogs.com/insus/p/10852906.html

 

DECLARE @ok_string NVARCHAR(MAX) DECLARE @WH VARCHAR(MAX) SET @WH='C1,C2,C3-C9,C20,C22,C30-C35' ;WITH HW AS ( SELECT [value] FROM [dbo].[tvf_ConvertStringToTable](@WH,',') ) --SELECT [value],[dbo].[svf_FillConnectionString]([value]) as [str] FROM HW SELECT @ok_string = ISNULL(@ok_string + ',', '') + [dbo].[svf_FillConnectionString]([value]) FROM HW SELECT @ok_string Source Code

 

大功告成！