MySQL中替换含段落分隔特定字符串的一步式解决方案求助

Got it, let's fix this with a cleaner, single-step SQL statement! The core issue here is that your initial REPLACE call couldn't account for the line break (and any extra whitespace) between (Amazon) and Marketplace. Instead of running two separate updates, we can use regex-based replacement to target the entire problematic string—including the line break—in one go.

Working SQL Statement (MySQL 8.0+) #

Use MySQL's REGEXP_REPLACE function, which lets you match patterns with special characters like line breaks:

UPDATE s_order 
SET `internalcomment` = REGEXP_REPLACE(
    `internalcomment`, 
    'magnalister-Verarbeitung \\(Amazon\\)\\s+Marketplace Bestellnummer: ', 
    ''
);

Breakdown of the Regex: #

• \\( and \\): Escape the parentheses since they're special characters in regular expressions (we need to match them literally).
• \\s+: Matches one or more whitespace characters—this covers line breaks (\n), carriage returns (\r), spaces, or tabs. It handles any spacing (including your specific line break) between the two parts of the string.

Fallback for Older MySQL Versions #

If you're using a MySQL version before 8.0 (which doesn't support REGEXP_REPLACE), you can chain REPLACE calls to target the line break explicitly:

UPDATE s_order 
SET `internalcomment` = REPLACE(
    REPLACE(`internalcomment`, 'magnalister-Verarbeitung (Amazon)\n', ''), 
    'Marketplace Bestellnummer: ', 
    ''
);

(Replace \n with \r\n if your system uses Windows-style carriage returns for line breaks.)

Critical Note #

Always back up your s_order table before running bulk UPDATE statements to avoid accidental data loss!

内容的提问来源于stack exchange，提问作者Christian Mazilu