hive regexp_extract weirdness

by

From the docs https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF it appears that regexp_extract() is a record/line extraction of the data you wish to extract.

It seems to work on a first found (then quit) as opposed to global. Therefore the index references the capture group.

0 = the entire match
1 = capture group 1
2 = capture group 2, etc …

Paraphrased from the manual: 

regexp_extract('foothebar', 'foo(.*?)(bar)', 2)
                                  ^    ^   
               groups             1    2

This returns 'bar'.

So, in your case, to get the text after the dot, something like this might work:
regexp_extract(name, '\.([^.]+)', 1)
or this
regexp_extract(name, '[.]([^.]+)', 1)

edit

I got re-interested in this, just a fyi, there could be a shortcut/workaround for you.

It looks like you want a particular segment separated with a dot . character, which is almost like split.
Its more than likely the regex engine used overwrites a group if it is quantified more than once.
You can take advantage of that with something like this:

Returns the first segment: abc.def.ghi
regexp_extract(name, '^(?:([^.]+)\.?){1}', 1)

Returns the second segment: abc.def.ghi
regexp_extract(name, '^(?:([^.]+)\.?){2}', 1)

Returns the third segment: abc.def.ghi
regexp_extract(name, '^(?:([^.]+)\.?){3}', 1)

The index doesn’t change (because the index still referrs to capture group 1), only the regex repetition changes.

Some notes:

  • This regex ^(?:([^.]+)\.?){n} has problems though.
    It requires there be something between dots in the segment or the regex won’t match ....

  • It could be this ^(?:([^.]*)\.?){n} but this will match even if there is less than n-1 dots,
    including the empty string. This is probably not desireable.

There is a way to do it where it doesn’t require text between the dots, but still requires at least n-1 dots.
This uses a lookahead assertion and capture buffer 2 as a flag.

^(?:(?!\2)([^.]*)(?:\.|$())){2} , everything else is the same.

So, if it uses java style regex, then this should work.
regexp_extract(name, '^(?:(?!\2)([^.]*)(?:\.|$())){2}', 1) change {2} to whatever ‘segment’ is needed (this does segment 2).

and it still returns capture buffer 1 after the {N}’th iteration.

Here it is broken down

^                # Begining of string
 (?:             # Grouping
    (?!\2)            # Assertion: Capture buffer 2 is UNDEFINED
    ( [^.]*)          # Capture buffer 1, optional non-dot chars, many times
    (?:               # Grouping
        \.                # Dot character
      |                 # or,
        $ ()              # End of string, set capture buffer 2 DEFINED (prevents recursion when end of string)
    )                 # End grouping
 ){3}            # End grouping, repeat group exactly 3 (or N) times (overwrites capture buffer 1 each time)

If it doesn’t do assertions, then this won’t work!

More Related Contents:

  1. How to extract all Yahoo emails from a .CSV file?
  2. Regular Expression for ” – ” and characters after [closed]
  3. What is a word boundary in regex?
  4. Regex to match only letters
  5. How to calculate the number of occurrence of a given character in each row of a column of strings?
  6. How can I use a variable in the replacement side of the Perl substitution operator?
  7. Why doesn’t `\d` work in regular expressions in sed? [duplicate]
  8. Regular expression for youtube links
  9. How can I use ‘Not Like’ operator in MongoDB
  10. Zero-Length regexes and infinite matches?
  11. Regex pattern for HH:MM:SS time string
  12. Comma Separated Numbers Regex
  13. RegEx pattern any two letters followed by six numbers
  14. sed join lines together
  15. Symbol for any number of any characters in regex?
  16. Remove everything except regex match in Vim
  17. Complete word matching using grepl in R
  18. AngularJS – Remove leading and trailing whitespace from input-box using regex
  19. Difference between egrep and grep
  20. Regex optional group
  21. Dollar sign in regular expression and new line character
  22. How to use RegEx in Dart?
  23. Regex for youtube URL
  24. What do \S, \W, \D stand for in regex?
  25. Using RegEX To Prefix And Append In Notepad++
  26. How to match the first word after an expression with regex?
  27. When is it best to use Regular Expressions over basic string splitting / substring’ing?
  28. Redirect all traffic to root of another domain
  29. ignoring changes matching a string in git diff
  30. Regex allow digits and a single dot

Leave a Comment