SQL Server拼图 - 在逗号之前复制逗号并替换字符串?
我在Table1中有几个字符串,我想根据表2中的值进行更新。SQL Server拼图 - 在逗号之前复制逗号并替换字符串?
示例代码
http://rextester.com/HQFOQ18215
IF OBJECT_ID('Test1','U') iS NOT NULL
DROP TABLE Test1;
IF OBJECT_ID('Test2','U') iS NOT NULL
DROP TABLE Test2;
Create table Test1
(
Id INT
,Lid INT
,MyString VARCHAR(MAX)
,Did INT
,Secid INT
);
INSERT INTO Test1 values (1,100,'you,shall,not,pass,gandlaf,the,grey', 401, 501);
INSERT INTO Test1 values (2,100,'ok,fine,bye', 401, 501);
INSERT INTO Test1 values (3,100,'test,dev,uat,prod', 403, 501);
INSERT INTO Test1 values (4,100,'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16', 404, 501);
Create table Test2
(
Id INT IDENTITY(1,1)
,SecId INT
,CommaPosition INT
,Value VARCHAR(50)
,Did INT
,RowId INT
);
INSERT INTO Test2 Values (501, 4, '[Quantity]', 401, 1);
INSERT INTO Test2 Values (501, 14, '[Price]', 401, 1);
INSERT INTO Test2 Values (501, 4, '[Quantity]', 401, 2);
INSERT INTO Test2 Values (501, 14, '[Price]', 401, 2);
INSERT INTO Test2 Values (501, 14, '[Quantity|Price]', 403, 3);
INSERT INTO Test2 Values (501, 4, '[Interest]', 404, 4);
INSERT INTO Test2 Values (501, 14, '[Expired]', 404, 4);
SELECT * FROM Test1;
SELECT * FROM Test2;
期望输出
/*
Expected OUTPUT
Id Lid MyString Did Secid
1 100 you,shall,not,[quantity],gandlaf,the,grey,,,,,,,[Price], 401 501
2 100 ok,fine,bye,[quantity],,,,,,,,,,[Price], 402 501
3 100 test,dev,uat,prod,,,,,,,,,,[Quantity|Price], 403 501
4 100 1,2,3,[Quantity],5,6,7,8,9,10,11,12,13,[Price],15,16 404 501
*/
第一串
you,shall,not,pass,gandlaf,the,grey
其中"pass"
是之前其通过[quantity]
从表2但是这并未替换第四逗号位置't h ave第14个逗号 所以逗号被复制直到它到达第14个逗号并且在第14个逗号[Price]
被替换之前。最终输出是"you,shall,not,[quantity],gandlaf,the,grey,,,,,,,[Price],"
第二个字符串
ok,fine,bye
没有逗号第四十四任逗号,所以第4逗号添加和[quantity]
之前在表2和最终被取代,然后加逗号,直到第14位的逗号位置列 BASD字符串将成为ok,fine,bye,[quantity],,,,,,,,,,[Price],
三串
test,dev,uat,prod
只有14逗号位置表2中可用,因此逗号被复制,直到14日逗号和14逗号之前[Quantity|Price]
串 加入最终的字符串变成test,dev,uat,prod,,,,,,,,,,[Quantity|Price],
在第四根弦
1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
如果你看到第4和第14次成为逗号可用,因此14日逗号之前的字符串被替换[Interest]
和字符串成为14逗号被[Expired]
替换而将余下的字符串不变。
我想实现上面的输出,我试过下面的update语句没有给我想要的结果。
UPDATE T1
SET T1.MyString = T1.MyString + REPLICATE(',',T2.CommaPosition - (len(T1.MyString) - LEN(REPLACE(T1.MyString,',',''))))
FROM Test1 as T1 INNER JOIN Test2 as T2 ON T1.Secid = T2.SecId AND T1.Did = T2.Did AND T1.Id = T2.RowId;
SELECT DISTINCT T1.Lid, T1.MyString, T1.Did, T1.Secid, T2.RowId
FROM Test1 as T1 INNER JOIN Test2 as T2 ON T1.Secid = T2.SecId AND T1.Did = T2.Did AND T1.Id = T2.RowId;
我假设你执行某种宏替换。
一对夫妇的注意事项:
- 后面的逗号似乎是不一致的,所以我离开它
- 你有价格的最后一排,而我有过期
示例
Select A.ID
,A.Lid
,B.MyString
,A.Did
,A.Secid
From Test1 A
Cross Apply (
Select MyString = Stuff((Select ',' +RetVal
From (
Select RetSeq
,RetVal = max(IsNull(B2.Value,B1.RetVal))
From (
Select Top (Select max(CommaPosition) From Test2 Where RowID=A.ID)
RetSeq=Row_Number() Over (Order By (Select null))
,RetVal=''
From master..spt_values
Union All
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(N.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(A.MyString,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
Cross Apply x.nodes('x') AS N(i)
) B1
Left Join Test2 B2 on B2.RowID=A.ID and B2.CommaPosition=B1.RetSeq
Group By B1.RetSeq
) B3
Order by RetSeq
For XML Path ('')),1,1,'')
) B
返回
正确答案是不中存储多个值一列。这违反了第一范式 !!!
如果将连接的字符串分解为父表和子表,最好。您的示例Table2
看起来是一组修改命令,它已经具有所需的结构 - 您只需将全套值放入永久表中即可。
一旦你有了这些,处理你的变化数据是非常简单的:加入关键值和更新!繁荣,完成。您的数据库应该进行规范化处理,不应该将数据以易于修改的数据存储在需要解压缩,修改并重新打包的格式中。
如果您需要某种方法将数值连接在一起以产生如MyString
的输出Table1
,请为此查看,或者在前端代码中执行此操作。有一个很好的基于SQL的解决方案,可以很容易地做到这一点。谨防随机XML PATH
您可能遇到的解决方案,因为大多数解决方案无法容纳包含类似xml的元素的字符串,例如>
和<
。使用下面这个例子中的一个(用.value()
表达式完成),其中可以使用来容纳这样的字符。
这是一个通用示例,显示如何将父表和子表合并到单个行中,每个父表与每个父表中的所有子值连接在一起。您可以使用FOR XML
将ORDER BY
子句放入查询中以获取特定顺序。
CREATE TABLE dbo.Parent (
ParentId int identity(1,1) NOT NULL CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED,
Name varchar(100) NOT NULL CONSTRAINT UQ_Parent_Name UNIQUE
);
CREATE TABLE dbo.ParentChild (
ParentId int NOT NULL,
ChildName varchar(100)
);
INSERT dbo.Parent (Name) VALUES
('Parent 1'), ('Parent X'), ('Parent B');
INSERT dbo.ParentChild (ParentId, ChildName) VALUES
(1, 'ABC'), (1, 'WHOAMAN'), (1, 'QRT'),
(2, 'XYZ'), (3, 'LMN'), (3, 'MAN'), (3, 'WHOADOG');
SELECT
p.*,
Children = Substring((
SELECT ', ' + ChildName
FROM ParentChild pc
WHERE p.ParentId = pc.ParentId
FOR XML PATH(''), TYPE
).value('.[1]', 'varchar(max)'), 3, 8000)
FROM
dbo.Parent p
WHERE -- an example of filtering by a child value without substrings on the combined string
EXISTS (
SELECT *
FROM dbo.ParentChild pc
WHERE
p.ParentId = pc.ParentId
AND pc.ChildName = 'WHOADOG'
);
这根本没有任何意义。为什么你首先有这些奇怪的分隔字符串?这违反了1NF并且破坏了关系数据的整个观点。 –