使用regexp_replace替换特殊符号时字符间出现空格，求原因及解决

Hey there, let's break down why you're getting that unexpected result:

The Core Issue #

Your regex has unescaped special regex metacharacters that are messing up the matching logic:

• The ( and ) in your pattern are not literal parentheses—they're regex grouping operators. When you write (|), it gets interpreted as "match an empty string OR a closing parenthesis".
• Empty string matches happen between every character in your input (before H, between H and e, between e and !, etc.), so replacing those empty matches with spaces splits every character apart.
• Also, you've duplicated the - in your pattern, which is redundant but not harmful on its own.

Fixed Solution #

The cleaner and safer way to match multiple literal special characters is to use a regex character class ([]). Inside a character class, most metacharacters lose their special meaning, so you don't need to escape ( and ) here.

Here's the corrected SQL (adjusted for common databases):

For PostgreSQL (requires the 'g' flag for global replacement):

select regexp_replace(column_name, '[-"~!()]+', ' ', 'g') from tablename;

For MySQL (global replacement is default, no flag needed):

select regexp_replace(column_name, '[-"~!()]', ' ') from tablename;

What This Does: #

• [-"~!()]: The character class matches any single character from the list (the - is placed first to avoid being interpreted as a range like a-z).
• + (in PostgreSQL example): Matches one or more consecutive special characters, so multiple symbols get replaced with a single space instead of multiple spaces.
• 'g' flag: Ensures all occurrences are replaced, not just the first one.

With this fix, your input He!lo will correctly become He lo instead of H e l o.

内容的提问来源于stack exchange，提问作者John Loe