展开中断或忽略的序号

网上有人问：

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

 

另一个自定义函数：

把字符串转换为表：

 

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

 

现在试使用上面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

差不多接近要求了，现在只要把[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

 

大功告成！