27,579
社区成员
发帖
与我相关
我的任务
分享
create FUNCTION [dbo].[FN_SplitData](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
declare @Questions varchar(MAX)='1-32|26,42-41|32'
;WITH CTE1 AS(
SELECT id, LEFT(val, CHARINDEX('-', val)-1) AS QuestionId, SUBSTRING(val, CHARINDEX('-', val)+1, 100) AS [IndexNumber]
, SUBSTRING(val, CHARINDEX('|', val)+1, 100) AS Numbers
FROM [dbo].[FN_SplitData](@Questions,',')
)
select QuestionId,[IndexNumber],Numbers from CTE1
--得到的是下面的结果,但是不是我想要的
/*
QuestionId IndexNumber Numbers
1 32|26 26
42 41|32 32
*/
--下面的才是我想要的
/*
QuestionId IndexNumber Numbers
1 32 26
42 41 32
*/
--求帮忙修改一下代码
DECLARE
@S VARCHAR(MAX)
,@Split1 CHAR(1)
,@Split2 CHAR(1)
,@Split3 CHAR(1)
,@X XML
SELECT
@S = '1-32|26,42-41|32'
,@Split1 = ','
,@Split2 = '-'
,@Split3 = '|'
SET @S = '<Question><QuestionID>'+REPLACE(@S,@Split1,'</Value></Question><Question><QuestionID>')
SET @S = REPLACE(@S,@Split2,'</QuestionID><IndexNum>')
SET @S = REPLACE(@S,@Split3,'</IndexNum><Value>')+'</Value></Question>'
SELECT @X = CONVERT(XML,'<root>' + @S + '</root>')
--SELECT @X
DECLARE @XML_Temp TABLE (XML_Content XML)
INSERT INTO @XML_Temp VALUES (@X)
SELECT
Node.value('(QuestionID)[1]', 'varchar(50)') AS 'QuestionID'
,Node.value('(IndexNum)[1]', 'varchar(50)') AS 'IndexNum'
,Node.value('(Value)[1]', 'varchar(50)') AS 'Value'
FROM
@XML_Temp
CROSS APPLY
@X.nodes('root/Question/.') AS Content(Node)
/*
QuestionId IndexNumber Value
1 32 26
42 41 32
*/