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

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

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