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

Question

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.

score 1 · Accepted Answer · answered Nov 21 '20 at 13:32

1

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.

answered Nov 21 '20 at 13:32

Wiktor Stribiżew

484,719
26
302
397

1

@thecardcaptor did you use ``\\`` before `*`? – Wiktor Stribiżew Nov 21 '20 at 14:29
sorry i was not using ?: it went great. – thecardcaptor Nov 21 '20 at 14:33
how about extracting the currency like CNY, EUR, AUD, IDR? – thecardcaptor Nov 22 '20 at 11:42
1

@thecardcaptor Replace `{3}` with `{2}`. See [this regex demo](https://regex101.com/r/2QDqXC/1). – Wiktor Stribiżew Nov 22 '20 at 11:54

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

1 Answers1