求验证两位十六进制字符串的正则表达式及Impala脏数据预处理方案

Alright, let's break down your two needs and give you practical, actionable solutions tailored to your scenario:

If you need to confirm an entire string is a valid two-digit hex value, use this regex:

^[0-9A-Fa-f]{2}$

Here's the breakdown of each component:

• ^ and $ anchor the match to the start and end of the string, ensuring no extra characters exist before or after the two-digit hex code.
• [0-9A-Fa-f] matches any valid hex character (0-9, uppercase A-F, lowercase a-f).
• {2} enforces exactly two characters, so it rejects shorter or longer strings outright.

If you just need to find two-digit hex fragments within a larger string (instead of validating the whole string), drop the anchors:

[0-9A-Fa-f]{2}

Dealing with Oracle BLOB data converted to Hadoop storage often means handling messy control characters and unreadable ASCII values. Here's how to prep your data so unhex works reliably:

Step 1: Strip Non-Hex Characters First #

First, remove any characters that aren't valid hex digits—unhex can only process 0-9/A-F/a-f, so non-hex characters will break the function:

regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', '')

This replaces all non-hex characters with an empty string, leaving only valid hex digits behind.

Step 2: Ensure Even Length for unhex #

unhex requires an even number of hex digits (each pair maps to one byte). If your cleaned string has an odd length, trim the last character to make it even:

substr(
  regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', ''),
  1,
  length(regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', '')) - mod(length(regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', '')), 2)
)

Step 3: (Optional) Remove Hex Codes for Unreadable/Control Characters #

If you specifically want to eliminate hex pairs that map to control characters (0x00-0x1F, 0x7F) or other unreadable values, add a regex replacement to strip those pairs first:

-- First remove control character hex pairs, then clean non-hex chars, then fix length
substr(
  regex_replace(
    regex_replace(your_blob_column_str, '(0[0-9A-F]|1[0-9A-F]|7F)', '', 'g'),
    '[^0-9A-Fa-f]', ''
  ),
  1,
  length(
    regex_replace(
      regex_replace(your_blob_column_str, '(0[0-9A-F]|1[0-9A-F]|7F)', '', 'g'),
      '[^0-9A-Fa-f]', ''
    )
  ) - mod(length(
    regex_replace(
      regex_replace(your_blob_column_str, '(0[0-9A-F]|1[0-9A-F]|7F)', '', 'g'),
      '[^0-9A-Fa-f]', ''
    )
  ), 2)
)

The '(0[0-9A-F]|1[0-9A-F]|7F)' pattern targets hex pairs for 0x00-0x1F (control characters) and 0x7F (delete character). The 'g' flag ensures all occurrences are replaced, not just the first.

Final Query Example #

Putting it all together to use with unhex:

SELECT
  unhex(
    substr(
      regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', ''),
      1,
      length(regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', '')) - mod(length(regex_replace(your_blob_column_str, '[^0-9A-Fa-f]', '')), 2)
    )
  ) AS cleaned_blob_data
FROM your_hadoop_table;

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