Help identifying and isolating a date held within a string of text

DomoNewb

Hi all, 

 

I'm hoping some creative minds out there can help with a business problem I am having. 

 

I am looking to create a Beast Mode field for expiry date, however the source of the expiry date is held within a string of text.
Example: "Building agreement in place with John Smith - signed via docusign , EXPIRES 2020-04-28 / John Smith aware of upcoming events"

 

The format is always the same at the expiry aprt (comma, space, "expires", space, DATE, space, /) so I can do it easily in Excel using Find and Replace. 

 

Does Domo have anything similar I can use to locate and isolate this date?

Thanks in advance!

jaeW_at_Onyx

 

InStr and CHARINDEX are common SQL functions that can get the job done.

you can use Regex expressions in Magic (Replace Text)

 

 

GrantSmith

Hi @DomoNewb 

 

There's a few ways to do this depending on how you want to process your data.

 

Using Magic ETL Replace Text tile:

This is assuming the date is ALWAYS in the YYYY-MM-DD format following "EXPIRED "

Here's a copyable version of the regular expression:

^.*EXPIRES (\d{4}-\d{2}-\d{2}).*$

 

 

Using a Beast Mode:

SUBSTRING(SUBSTRING(`text_col`, INSTR(`text_col`,' EXPIRES ') + LENGTH(' EXPIRES ')), 1, 10)

DomoNewb

Awesome, it sounds like that will work for me! Thanks