如何在PostgreSQL中删除地址字段内#及后续至逗号前的内容？

Got it, let's sort out this address cleaning task. From your example, you need to strip two specific parts from each address entry: the space right before # plus the # itself, and everything between that # and the next comma—while keeping the comma intact.

Solution: Use Regular Expressions #

Regex is perfect for this kind of targeted text replacement. Here's how to implement it across common tools:

1. Python (for datasets in pandas or raw strings)

If you're working with Python, use the built-in re module to handle the replacement globally:

import re

original_address = "ADDRESS 123 abc st. #123, city, zipcode 321 def road #321, city, zipcode"
cleaned_address = re.sub(r'\s#.*?(?=,)', '', original_address)
print(cleaned_address)
# Output: ADDRESS 123 abc st., city, zipcode 321 def road, city, zipcode

Regex breakdown to help you adjust for edge cases:

• \s#: Matches the space immediately preceding # and the # character itself
• .*?(?=,): Non-greedily matches any characters until it hits a comma (the (?=,) is a positive lookahead that ensures the comma stays in your final text)
• Replacing this matched pattern with an empty string removes exactly what you don't want.

2. Excel (365/2021 or newer)

Use Excel's REGEXREPLACE function (available in modern versions) to apply the cleaning directly in your spreadsheet:

=REGEXREPLACE(A1, "\s#.*?(?=,)", "")

Replace A1 with the cell holding your original address, then drag the formula down to apply it to the entire column.

3. SQL (MySQL 8+/PostgreSQL)

MySQL:

SELECT REGEXP_REPLACE(address_column, '\\s#.*?(?=,)', '') AS cleaned_address
FROM your_table;

(Note: MySQL requires double backslashes to escape regex special characters)

PostgreSQL:

SELECT REGEXP_REPLACE(address_column, '\s#.*?(?=,)', '', 'g') AS cleaned_address
FROM your_table;

The 'g' flag ensures all matching segments in each address are replaced, not just the first one.

Quick Edge Case Note #

If your addresses ever have # without a following comma (unlikely in your sample), you could tweak the regex to handle that, but based on your provided example, the above solutions will work flawlessly.

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