Teradata SQL中REGEXP_REPLACE合并处理末尾日期的方法

Absolutely! You can absolutely collapse all three of your date-ending patterns into a single REGEXP_REPLACE call. Let's walk through how to build it and why it works.

The Regex Breakdown #

We need a pattern that matches all three date variants at the end of your note text, while leaving the rest of the content intact. Here's the final expression:

REGEXP_REPLACE(your_note_column, '\\s+(\\d{4}/)?\\d{2}[A-Z]{3}(\\d{2})?$', '')

Let's break down each component to make sense of it:

• \\s+: Matches one or more spaces (ensures we're targeting a date segment separated from the main note content)
• (\\d{4}/)?: An optional group for the YYYY/ prefix (like 2004/ in your second example). The ? means this part can appear 0 or 1 times.
• \\d{2}[A-Z]{3}: The core date component—two digits (day) plus three uppercase letters (month abbreviation, e.g., FEB). This covers all three scenarios' base date part.
• (\\d{2})?$: An optional group for the two-digit year suffix (like 18 in your third example). The $ anchors the entire pattern to the end of the string, so we only remove dates that are the final part of the note.

Example Tests #

Let's apply this to your sample texts to verify:

1. Input: ADDED EMAIL ADDRESS FROM CTCE DATA 21FEB → Output: ADDED EMAIL ADDRESS FROM CTCE DATA
2. Input: REX AUTO REFUND 2004/21FEB → Output: REX AUTO REFUND
3. Input: CANCELED DUPLICATED SEGMENTS P 21FEB18 → Output: CANCELED DUPLICATED SEGMENTS P

Bonus: Case Insensitivity #

If your note text might have lowercase month abbreviations (e.g., 21feb instead of 21FEB), add the (?i) flag to make the regex case-insensitive:

REGEXP_REPLACE(your_note_column, '(?i)\\s+(\\d{4}/)?\\d{2}[A-Z]{3}(\\d{2})?$', '')

A quick reminder: Teradata uses POSIX-style regular expressions, so we need double backslashes (\\) for escape sequences (like \\s for whitespace) instead of single ones.

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