Python正则表达式条件提取SQLCODE及特殊场景下SQLSTATE子串的实现方案咨询

Let's tackle this problem step by step. Your original regex fails on the 4th case because it captures any non-punctuation/non-whitespace characters after SQLCODE:, which isn't restricted to valid 4-digit integers. Here's a robust approach that meets your exact requirements:

Approach #

1. Prioritize valid 4-digit SQLCODE: First, check if SQLCODE: is followed by exactly 4 digits. If this pattern exists, we use this value immediately.
2. Fallback to SQLSTATE suffix: If no valid 4-digit SQLCODE is found, look for a SQLSTATE starting with T and extract the numeric portion right after the T.

Implementation Code #

import re

def extract_error_code(err_log):
    # Try to match a 4-digit SQLCODE first (accounts for optional spaces after colon)
    sqlcode_match = re.search(r'SQLCODE:\s*(\d{4})', err_log)
    if sqlcode_match:
        return sqlcode_match.group(1)
    
    # Fallback: extract digits after 'T' in SQLSTATE
    sqlstate_match = re.search(r'SQLSTATE:\s*T(\d+)', err_log)
    if sqlstate_match:
        return sqlstate_match.group(1)
    
    # Handle edge cases where neither pattern matches (adjust return value as needed)
    return None

Test with Your Examples #

Let's verify this function works with your test cases:

• Input: SQLSTATE: 22018, SQLCODE: 3535. → Output: 3535
• Input: SQLSTATE: 40001, SQLCODE: 2631 Session Id 629709103 → Output: 2631
• Input: SQLSTATE: 40001, SQLCODE: 2631 Session Id 594700603 → Output: 2631
• Input: SQLSTATE: T7547, SQLCODE: 754Session Id613234380 → Output: 7547

Explanation #

• The first regex SQLCODE:\s*(\d{4}) uses \s* to handle any number of spaces (including zero) after SQLCODE:, and \d{4} ensures we only capture valid 4-digit integers.
• The fallback regex SQLSTATE:\s*T(\d+) targets SQLSTATE values starting with T, capturing all numeric characters after the T—perfect for your edge case where SQLCODE doesn't provide a valid 4-digit number.
• This logic ensures we always return the correct value per your rules, even for messy edge cases where SQLCODE is truncated or followed by unexpected text.

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