how to get all strings between nth occurance with 2 different pattern

i have table, one of the field has value like this :

LLC1R.8888.GR0054656*DR.798012...2..............GR0054656*CNY*ARTIST*
TC1R.88.GR0054656*DR.765012...2..............GR0054656*EUR*LUKE*
K56R.8.GR0054656*DR.258812...4..............GR0054656*AUD*
790GG.88.GR0054656*DR.338012...5..............GR0054656*IDR*MATTHEW*
GREFD.888.GR0054656*DR.198012...6..............GR0054656*

Expexcting result :

ARTIST

LUKE

MATTHEW

How to extract between 3rd and 4th and make the other column safe, because the result will be use in case when ?

Any ideas/help will be appreciated. Thank you.

Solution

You can use

REGEXP_EXTRACT(col, '^(?:[^*]*\\*){3}([^*]*)', 1)

Details

^ - start of string
(?:[^*]*\*){3} - three occurrences of any chars other than * and then a * char
([^*]*) - Group 1: any zero or more chars other than *.

The index argument is set to 1 so as to extract Group 1 value.

Note that (?:[^*]*\*) is a non-capturing group that does not capture (store in a separate memory buffer) the substring it matches.