Regex for getting a string within characters, with first character set optional in Presto/Athena

user_14329561 0
Viewed 539

I've been banging my head around this one all, day. I got it to work in various online regex tools, but whenever I use it on the query the result is wrong.

I have data in my DB like this:

AMAZON PAYMENTS EUROPE S.C.A.
1/asdfL GE#EFRDA^9212 GRIFF
Frau HUSEL G^9212 GRIFF

I want to extract the text within 1/ and ^ when they are present. Like this:

AMAZON PAYMENTS EUROPE S.C.A.
asdfL GE#EFRDA
Frau HUSEL G

I tried many variations, and they all work online:

  • Non Capturing group: ^(?:1\/)?(.*?(?=\^|$))
  • Backward Looking: (?<=1\/)(.*?(?=\^|$))
  • Conditional Expression: (?(?=^1)1/(.*?(?=\^|$))|((^.*?(?=\^|$))))

But when I run the query in the AWS I always get the 1/ back in the extract.

Anyone has a clue on how to go around this?

presto amazon-athena regex
1 Answers

I would use REGEXP_REPLACE here:

regexp_replace([column], '^(?:[^/]*/)?([^^]*)\^.*', '$1')

See the regex demo.

Details:

  • ^ - start of string
  • (?:[^/]*/)? - an optional sequence matching any zero or more chars other than a / and then a / char
  • ([^^]*) - Capturing group 1 (referred to from the replacement pattern with $1): any zero or more chars other than a ^
  • \^ - a literal ^ char
  • .* - the rest of the string to the end.
user_38329700
Related