SQLServer：分组并使用其他表中的数据替换COLUMN值，而不使用UDF(SQLServer : Grouping and Replacing a COLUMN value with the D

SQLServer：分组并使用其他表中的数据替换COLUMN值，而不使用UDF(SQLServer : Grouping and Replacing a COLUMN value with the DATA from other table, without UDF)

我想用@ModTable表中的等效文本替换@CommentsTable列“Comments”中的数字，而不在单个SELECT中使用UDF。 可能与CTE。 用REPLACE尝试了STUFF，但没有运气。

任何建议将是一个很大的帮助！

样品：

DECLARE @ModTable TABLE ( ID INT, ModName VARCHAR(10), ModPos VARCHAR(10) ) DECLARE @CommentsTable TABLE ( ID INT, Comments VARCHAR(100) ) INSERT INTO @CommentsTable VALUES (1, 'MyFirst 5 Comments with 6'), (2, 'MySecond comments'), (3, 'MyThird comments 5') INSERT INTO @ModTABLE VALUES (1, '[FIVE]', '5'), (1, '[SIX]', '6'), (1, '[ONE]', '1'), (1, '[TWO]', '2') SELECT T1.ID, <<REPLACED COMMENTS>> FROM @CommentsTable T1 GROUP BY T1.ID, T1.Comments **Expected Result:** ID Comments 1 MyFirst [FIVE] Comments with [SIX] 2 MySecond comments 3 MyThird comments [FIVE]

I would like to replace the numbers in @CommentsTable column "Comments" with the equivalent text from @ModTable table, without using UDF in a single SELECT. May with a CTE. Tried STUFF with REPLACE, but no luck.

Any suggestions would be a great help!

Sample:

DECLARE @ModTable TABLE ( ID INT, ModName VARCHAR(10), ModPos VARCHAR(10) ) DECLARE @CommentsTable TABLE ( ID INT, Comments VARCHAR(100) ) INSERT INTO @CommentsTable VALUES (1, 'MyFirst 5 Comments with 6'), (2, 'MySecond comments'), (3, 'MyThird comments 5') INSERT INTO @ModTABLE VALUES (1, '[FIVE]', '5'), (1, '[SIX]', '6'), (1, '[ONE]', '1'), (1, '[TWO]', '2') SELECT T1.ID, <<REPLACED COMMENTS>> FROM @CommentsTable T1 GROUP BY T1.ID, T1.Comments **Expected Result:** ID Comments 1 MyFirst [FIVE] Comments with [SIX] 2 MySecond comments 3 MyThird comments [FIVE]

最满意答案

创建一个光标，跨越@ModTable并每次更换一次

DECLARE replcursor FOR SELECT ModPos, ModName FROM @ModTable; OPEN replcursor; DECLARE modpos varchar(100) DEFAULT ""; DECLARE modname varchar(100) DEFAULT ""; get_loop: LOOP FETCH replcursor INTO @modpos, @modname SELECT T1.ID, REPLACE(T1.Comments, @modpos, @modname) FROM @CommentsTable T1 GROUP BY T1.ID, T1.Comments END LOOP get_loop;

当然，您可以将结果存储在临时表中，并在循环结束时完全得到结果。

=> CLR Function()

As I have lot of records in "CommentsTable" and the "ModTable" would have multiple ModName for each comments, finally decided to go with CLR Function. Thanks all of you for the suggestions and pointers.