如何编写Hive Case语句去除符合特定规则的字段后缀？

Nice catch on the repetitive LIKE clauses—let's simplify this with Hive's regex support to cover all your required cases in one go, while keeping the logic clean and maintainable.

The Core Problem #

You want to strip the last 3 characters from MBRCHR (after trimming whitespace and uppercasing) only if those 3 characters follow this pattern:

• First character: 8 or 9
• Second character: Any digit (0-9)
• Third character: Any letter from A to E

Optimized Solution #

Instead of enumerating every possible combination with LIKE, use regexp_like to match the pattern in one condition:

CASE
    WHEN regexp_like(Upper(Trim(MBRCHR)), '[89][0-9][A-E]$')
    THEN substr(Upper(Trim(MBRCHR)), 1, length(Upper(Trim(MBRCHR))) - 3)
    ELSE Upper(Trim(MBRCHR))
END AS cleaned_MBRCHR

Let's break down what each part does:

• Upper(Trim(MBRCHR)): Standardizes the input by removing leading/trailing spaces and converting to uppercase—critical for consistent pattern matching.
• regexp_like(..., '[89][0-9][A-E]$'): The regex pattern checks the end of the string:
  • [89]: Matches either 8 or 9 as the first character of the suffix
  • [0-9]: Matches any single digit for the second character
  • [A-E]: Matches any uppercase letter from A to E for the third character
  • $: Ensures we're only matching characters at the end of the string (avoids accidentally matching similar patterns in the middle)
• The THEN clause uses substr to chop off the last 3 characters when the pattern matches; otherwise, it returns the standardized original string.

Bonus: Reduce Redundant Calculations #

If you want to avoid repeating Upper(Trim(MBRCHR)) (which makes the code cleaner and slightly more efficient), use a CTE or subquery to pre-process the string:

WITH processed_records AS (
    SELECT
        MBRCHR,
        Upper(Trim(MBRCHR)) AS standardized_MBRCHR
    FROM your_target_table
)
SELECT
    MBRCHR,
    CASE
        WHEN regexp_like(standardized_MBRCHR, '[89][0-9][A-E]$')
        THEN substr(standardized_MBRCHR, 1, length(standardized_MBRCHR) - 3)
        ELSE standardized_MBRCHR
    END AS cleaned_MBRCHR
FROM processed_records

This approach is easier to read and modify later—if you need to adjust the pre-processing step, you only change it once.

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