Search code examples
sqlregexmatchstring-matchingvertica

Is there a Regex to get 5th occurence in a following string?

I have a following string (well, UTM actually):

bla-ble-blo-bli-this-bad-blau-bleu
bla-ble-blo-bli-this_good-blau-bleu

5th position in UTM is called "Subject", and currently since I am using SQL to do any sort of transformations, I am using a very simple set of rules and some look-up table as I am splitting string on "-", but as you see, it fails on "this-bad" as it has a "-", but it works as expected on "this_good".

Is there a regex to get 5th occurence of this "Subject" part? I tried following regex, but I really don't know how to set it find only 5th occurence. The regex in question: \w+(?=-[^-]*$).

Thank you!

Solution

  • Remove the tail and head, leaving the 5th part:

    select regexp_replace(regexp_replace('bla-ble-blo-bli-this-bad-blau-bleu', '(-[^-]+){2}$', ''), '([^-]+-){4}', '')

    See live demo.