CODEWITHSUNDEEP
regexhiveextract
Leigh
Leigh

Reputation: 21

Hive REGEXP_EXTRACT returning null results

I am trying to extract R7080075 and X1234567 from the sample data below. The format is always a single upper case character followed by 7 digit number. This ID is also always preceded by an underscore. Since it's user generated data, sometimes it's the first underscore in the record and sometimes all preceding spaces have been replaced with underscores.

I'm querying HDP Hive with this in the select statement: 

REGEXP_EXTRACT(column_name,'[(?:(^_A-Z))](\d{7})',0)

I've tried addressing positions 0-2 and none return an error or any data. I tested the code on regextester.com and it highlighted the data I want to extract. When I then run it in Zepplin, it returns NULLs.

My regex experience is limited so I have reviewed the articles here on regexp_extract (+hive) and talked with a colleague. Thanks in advance for your help.

Sample data:

Sept Wk 5 Sunny Sailing_R7080075_12345
Holiday_Wk2_Smiles_X1234567_ABC

Upvotes: 0

Views: 845

Answers (1)

Vasan
Vasan

Reputation: 4956

The Hive manual says this:

Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.

Also, your expression includes unnecessary characters in the character class.

Try this:

REGEXP_EXTRACT(column_name,'_[A-Z](\\d{7})',0)

Since you want only the part without underscore, use this:

REGEXP_EXTRACT(column_name,'_([A-Z]\\d{7})',1)

It matches the entire pattern, but extracts only the second group instead of the entire match.

Or alternatively:

REGEXP_EXTRACT(column_name,'(?<=_)[A-Z]\\d{7}', 0)

This uses a regexp technique called "positive lookbehind". It translates to : "find me an upper case alphabet followed by 7 digits, but only if they are preceded by an _". It uses the _ for matching but doesn't consider it part of the extracted match.

Upvotes: 1

Related Questions