How can I create a beastmode that will return the desired substring in a field?

levi_kim

Hi all,

I've given this multiple attempts and just can't seem to get it. I have a field called 'Destination' that I want to use in a Beastmode that will return the first substring of "B-FGI" or "FGI" in a value such as "Doe, John A-B-FGI-20.15.0003.B-FGI" or "Doe, John A-FGI-20.15.0003.B-FGI". I also want to create another Beastmode in that same value that will return the locator number and following sub inventory: "Doe, John A-B-FGI-20.15.0003.B-FGI". The substring can also end in "FGI" such as "Doe, John A-B-FGI-20.15.0003.FGI"

Note: Value lengths are inconsistent. The hyphen position and count is also inconsistent as I want to return "B-FGI" or "FGI". I will include some examples below and bold the substrings I'd like to extract. So far I've tried SPLIT_PART but since there's a '-' in between the substring so it does not seem to work. Functions such as REGEX, POSITION, INDEX do not seem to work in Domo or maybe I am using it wrong.

Column Name: Destination

1st beastmode

Doe, John A-B-FGI-20.17.0003.B-FGI
Walker, Mary C-FGI-19.12.0003.FGI

Doe, Jane-B-FGI-RLB.02.01.B-FGI

2nd beastmode

Doe, John A-B-FGI-20.17.0003.B-FGI
Walker, Mary A-FGI-19.12.0003.FGI
Doe, Jane-B-FGI-RLB.02.01.B-FGI

GrantSmith

Because you have variable length in your string I'd recommend utilizing a formula tile in a Magic ETL to perform a regular expression.

FGI

REGEP_REPLACE(`Destination`, '^[^\-]*\-((\w\-)?FGI)-(\d{2}\.\d{2}\.\d{4}.*)$', '$1')

Tail end:

REGEX_REPLACE(`Destination`, '^[^\-]*\-((\w\-)?FGI)-(\d{2}\.\d{2}\.\d{4}.*)$', '$3')

You can play around with the regular expressions at websites like

https://regex101.com/