SQL Server Puzzle - 在逗号之前复制逗号并替换字符串？

I've few strings in Table1 which i want to update based on values available in Table2.

我在Table1中的字符串很少，我想根据表2中的值更新。

Sample code

示例代码

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

预期产出

/*

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
*/

First string you,shall,not,pass,gandlaf,the,grey where "pass" is before 4th comma position which is replaced by [quantity] from table2 but this doesn't have 14th comma so comma is replicated till it reaches the 14th comma and before 14th comma [Price] has been replaced. Final output is "you,shall,not,[quantity],gandlaf,the,grey,,,,,,,[Price],"

第一个字符串，你，不，传递，gandlaf，灰色，其中“pass”在第4个逗号位置之前，由table2的[quantity]替换，但是这个没有第14个逗号，所以逗号被复制直到它到达第14个逗号和之前的第14个逗号[Price]已被替换。最终输出是“你，将，不，[数量]，gandlaf，the，grey ,,,,,,,, [Price]，”
Second string ok,fine,bye doesn't have 4th comma either 14th comma, so 4th comma is added and before that [quantity] is substituted and then comma added till 14th position basd on Comma position column in Table2 and final string becomes ok,fine,bye,[quantity],,,,,,,,,,[Price],

第二个字符串ok，罚款，再见没有第14个逗号，或者第14个逗号，所以添加了第4个逗号，在此之前[数量]被替换，然后逗号添加到第14个位置，基于Table2中的逗号位置列，最后的字符串变为ok，细，再见，[数量] ,,,,,,,,,, [价格]
Third-string test,dev,uat,prod only 14th comma position is available in Table2, so comma is replicated till 14th commas and before 14th comma [Quantity|Price] string is added final string becomes test,dev,uat,prod,,,,,,,,,,[Quantity|Price],

第二个字符串测试，dev，uat，prod只有第14个逗号位置在表2中可用，所以逗号被复制到第14个逗号并且在第14个逗号之前[Quantity | Price]字符串被添加最终字符串变为test，dev，uat，prod ,, ,,,,,,,, [数量|价格]
In 4th string 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16 if you see 4th and 14th become commas are available so string before 14th comma is replaced by [Interest] and string become 14th comma is replaced by [Expired] keeping the remaining string untouched.

在第4个字符串1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16，如果你看到第4和第14个成为逗号是可用的所以字符串在第14个逗号之前是替换为[Interest]并且字符串变为第14个逗号被[Expired]替换，保持其余字符串不变。

I want to achieve above output, I tried below update statement is not giving me desired results.

我想实现上面的输出，我试过下面的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;

2 个解决方案

#1

I'm assuming you performing some sort of macro substition.

我假设你正在进行某种宏观替代。

A couple of note:

几点说明：

The Trailing Comma seems to be inconsistent, so I left it out
尾随逗号似乎不一致，所以我把它留了出来
You have Price in the final row, while I have Expired
你有最后一行的价格，而我已经过期了

Example

例

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

Returns

#2

The correct answer is don't store multiple values in a single column. This violates first normal form!!!

正确答案是不要在单个列中存储多个值。这违反了第一个正常形式！

It's best if you decompose your concatenated strings into a parent and a child table. Your example Table2 which appears to be a set of modification commands already has the required structure--you just need to put the full set of values into a permanent table just like it.

最好将连接的字符串分解为父表和子表。您的示例Table2似乎是一组修改命令，已经具有所需的结构 - 您只需将完整的值集合放入永久表中即可。

Once you have that, processing your change data is super easy: join on key values and update! Boom, done. Your database should be normalized and should not store data that is prone to modification in a format that requires unpacking it, modifying it, and packing it again.

完成后，处理您的更改数据非常简单：加入关键值并更新！热潮，完成了。您的数据库应该规范化，不应该存储易于修改的数据，这些数据需要解压缩，修改并重新打包。

If you need some way to concatenates values together to produce output like MyString in Table1, make a view for that, or do it in front-end code. There's a wonderful SQL-based solution with FOR XML PATH that can do this easily. Beware of random XML PATH solutions that you might run across as most of the ones out there can't accommodate strings that contain xml-like elements such as > and <. Use the one from this example below (complete with the .value() expression) which can accommodate such characters.

如果您需要某种方法将值连接在一起以生成Table1中的MyString之类的输出，请为其创建视图，或者在前端代码中执行此操作。有一个很棒的基于SQL的FOR XML PATH解决方案可以很容易地做到这一点。请注意您可能遇到的随机XML PATH解决方案，因为大多数解决方案无法容纳包含类似xml的元素的字符串，例如>和<。使用下面这个例子中的一个（带有.value（）表达式），它可以容纳这些字符。

This is a generic example that shows how to combine a parent and child table into single rows, one for each parent, with all the child values concatenated together. You can put an ORDER BY clause into the query using FOR XML to get a particular order.

这是一个通用示例，显示如何将父表和子表组合成单行，每个父表一行，所有子值连接在一起。您可以使用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'
   );

#1