34,576
社区成员
发帖
与我相关
我的任务
分享
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'
请教一下有没有简单点的办法
USE tempdb
GO
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL DROP FUNCTION [dbo].[Fun_Split]
GO
CREATE FUNCTION [dbo].[Fun_Split](@str NTEXT, @split NVARCHAR(10))
RETURNS @table TABLE (rid INT IDENTITY(1,1) PRIMARY KEY,[item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
------- 以上是 切分字符串表值函数, 如是 SQL Server2016+,可以不用创建 --------------------
IF OBJECT_ID('dbo.Fun_GetPara') IS NOT NULL
DROP FUNCTION dbo.Fun_GetPara
GO
-- =============================================
-- Author: yenange
-- Create date: 2019-05-26
-- Description: 获取字符串中的相关参数
-- =============================================
CREATE FUNCTION dbo.Fun_GetPara
(
@str VARCHAR(50)
)
RETURNS
@r TABLE
(
word VARCHAR(20),
minV VARCHAR(20),
maxV VARCHAR(20)
)
AS
BEGIN
DECLARE @tmpStr VARCHAR(20),@char VARCHAR(10),@minV varchar(20),@maxV VARCHAR(20)
SET @str=UPPER(LTRIM(RTRIM(@str)))
SET @char=''
SET @minV=''
SET @maxV=''
WHILE len(@str)>0
BEGIN
SET @tmpStr=LEFT(@str,1)
IF @tmpStr='-'
BEGIN
SET @maxV=SUBSTRING(@str, LEN(@char)+2,LEN(@str))
BREAK;
END
IF @tmpStr>='A' AND @tmpStr<='Z'
BEGIN
SET @char=@char+@tmpStr
END
IF @tmpStr>='0' AND @tmpStr<='9'
BEGIN
SET @minV=@minV+@tmpStr
END
SET @str=SUBSTRING(@str,2,LEN(@str))
END
INSERT INTO @r VALUES (@char,@minV,CASE WHEN @maxV>'' THEN @maxV ELSE @minV end);
RETURN
END
GO
---------------- 以上是分离出字符串的函数 -----------------------------
declare @WH varchar(MAX)
set @WH='C1,C2,C3-C9,C20,C22,C30-C35,RS1-RS6,N1,N6,D6-D9'
;WITH cte AS (
SELECT * FROM [dbo].[Fun_Split](@wh,',') AS f CROSS APPLY dbo.Fun_GetPara(f.item) AS ff
)
--SELECT * FROM cte
SELECT STUFF(
(
SELECT ','+ cte.word + LTRIM(ff.number)
FROM cte
CROSS APPLY (
SELECT * FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND
sv.number BETWEEN cte.minV AND cte.maxV
) AS ff
FOR XML PATH('')
),1,1,'')
/*
C1,C2,C3,C4,C5,C6,C7,C8,C9,C20,C22,C30,C31,C32,C33,C34,C35,RS1,RS2,RS3,RS4,RS5,RS6,N1,N6,D6,D7,D8,D9
*/
上面的代码考虑了不固定字符的情况。USE tempdb
GO
IF OBJECT_ID('[dbo].[Fun_Split]') IS NOT NULL DROP FUNCTION [dbo].[Fun_Split]
GO
CREATE FUNCTION [dbo].[Fun_Split](@str NTEXT, @split NVARCHAR(10))
RETURNS @table TABLE (rid INT IDENTITY(1,1) PRIMARY KEY,[item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
------- 以上是 切分字符串表值函数, 如是 SQL Server2016+,可以不用创建 --------------------
declare @WH varchar(MAX)
set @WH='C1,C2,C3-C9,C20,C22,C30-C35'
;WITH cte AS (
SELECT *,replace(replace(item,'C',''),'-','.') AS itemV FROM [dbo].[Fun_Split](@wh,',') AS f
),cte2 AS (
SELECT *,PARSENAME(itemV,2) AS minV,PARSENAME(itemV,1) AS maxV FROM cte
)
SELECT STUFF(
(
SELECT ',C'+ LTRIM(ff.number)
FROM cte2
CROSS APPLY (
SELECT * FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND
sv.number BETWEEN isnull(cte2.minV,cte2.maxV) AND cte2.maxV
) AS ff
FOR XML PATH('')
),1,1,'')
/*
C1,C2,C3,C4,C5,C6,C7,C8,C9,C20,C22,C30,C31,C32,C33,C34,C35
*/