Search code examples
looker-studio

Extract substring after second last colon

I am having a set of strings like this in a report field:

cpe:/o:redhat:enterprise_linux:7

cpe:/o:centos:centos:7

I need to extract the words after the second last colon ":"

ie.cpe:/o:centos:centos:7 -> should transform as centos:7
  cpe:/o:redhat:enterprise_linux:7 -> should transform as enterprise_linux:7

There are some regular expressions in like

REGEXP_EXTRACT(string,[^\:]+$)

This gives me the last word after : but not the second last word.

What can I try next?

Solution

  • You may use the following regex pattern:

    [^:]+:[^:]+$

    Updated code:

    REGEXP_EXTRACT(string, [^:]+:[^:]+$)

    Here is a regex demo showing that the pattern is working against your sample text.