如何在Hive中使用reg_extract或split提取#后的首个非空字符串？解决reg_extract返回NULL的问题

I’ve dealt with this exact regex quirk in Hive before—those leading hashes can throw off your pattern matching if you don’t account for them properly. Let’s get your extraction working.

Why Your reg_extract Returned NULL #

I assume you meant regexp_extract here. The issue was likely your regex pattern not accounting for leading # characters. A simple [^#]+ without anchoring to the string start won’t target the first valid segment after those leading hashes correctly, leading to NULL results.

Working regexp_extract Implementation #

Since Hive supports regexp_extract, we can build a regex that skips leading hashes and captures the first sequence of non-# characters. Here’s the code:

SELECT 
  regexp_extract(col_name, '^#*([^#]+)', 1) AS first_segment
FROM your_table;

Regex Breakdown:

• ^: Anchors the match to the start of the string (critical for targeting the first valid segment)
• #*: Matches 0 or more leading # characters (handles all cases: strings starting with no hashes, one hash, or multiple hashes)
• ([^#]+): Captures the first sequence of one or more non-# characters (this is the value we want)
• The final 1 tells Hive to return the first captured group

Testing with Your Sample Data #

Let’s verify this against your records:

• Input: ##214628##564#7576#7876 → Output: 214628
• Input: #12771#242###256823 → Output: 12771
• Input: ###3264###7236473####3 → Output: 3264

This matches your expected results exactly.

Optional: Convert to Numeric Type #

If you need the result as a number (since your sample outputs are integers), wrap the extraction in cast() (older Hive versions may not support to_number):

SELECT 
  cast(regexp_extract(col_name, '^#*([^#]+)', 1) AS bigint) AS first_segment_num
FROM your_table;

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