内容简介:网上有人问:declare @WH varchar(MAX)set @WH='C1,C2,C3-C9,C20,C22,C30-C35'
网上有人问:
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
大功告成!
以上所述就是小编给大家介绍的《展开中断或忽略的序号》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!
猜你喜欢:- 【Mysql进阶技巧(2)】利用mysql生成唯一序号
- LeetCode每日一题: Excel表列序号(No.171)
- LeetCode - 171 - Excel表列序号(excel-sheet-column-number)
- Linux中断一网打尽(2) - IDT及中断处理的实现
- Kernel trace tools(一):中断和软中断关闭时间过长问题追踪
- Java中断机制
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
The Book of CSS3
Peter Gasston / No Starch Press / 2011-5-13 / USD 34.95
CSS3 is the technology behind most of the eye-catching visuals on the Web today, but the official documentation can be dry and hard to follow. Luckily, The Book of CSS3 distills the heady technical la......一起来看看 《The Book of CSS3》 这本书的介绍吧!