SQL合并两表列数据并去除
字符问题求助

Got it, let's tackle those annoying 
 characters showing up in your concatenated commands. First, a quick breakdown: that's the XML-encoded version of a carriage return (ASCII character 13). It's either hiding in your history_detail.commands data already, or the FOR XML PATH('') method is escaping it during the concatenation process.

Here's a revised version of your query that strips out those carriage returns entirely:

SELECT 
    t1.HId,
    commands = STUFF(
        (
            SELECT DISTINCT ',' + REPLACE(t2.commands, CHAR(13), '') 
            FROM history_detail t2 
            WHERE t1.HId = t2.History_HID 
            FOR XML PATH('')
        ), 1, 1, ''
    )
FROM history t1
JOIN history_detail t2 ON t1.HId = t2.History_HID -- Switched to explicit JOIN for better readability
GROUP BY t1.HId

Key improvements made: #

• Added REPLACE(t2.commands, CHAR(13), '') in the subquery to remove carriage returns directly from the source data before concatenation. This stops the 
 from ever appearing in the final result.
• Updated the table join syntax from comma-separated tables to an explicit JOIN—this is a best practice that makes your query easier to read and avoids accidental cross-join mistakes.

If your data also has line feeds (ASCII 10, which usually pairs with carriage returns), you can nest the REPLACE functions to get rid of both:

REPLACE(REPLACE(t2.commands, CHAR(13), ''), CHAR(10), '')

Another pro tip: If you want to prevent other special characters (like <, >, or &) from being XML-encoded in your results, you can use the TYPE directive with .value() to return raw string values instead of XML-encoded text. Here's that enhanced version:

SELECT 
    t1.HId,
    commands = STUFF(
        (
            SELECT DISTINCT ',' + REPLACE(t2.commands, CHAR(13), '') 
            FROM history_detail t2 
            WHERE t1.HId = t2.History_HID 
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
    )
FROM history t1
JOIN history_detail t2 ON t1.HId = t2.History_HID
GROUP BY t1.HId

This ensures your commands stay exactly as they are (minus the carriage returns) without any unexpected XML encoding.

内容的提问来源于stack exchange，提问作者Puneet Solanki