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

DomoNewb · October 2020

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 · October 2020

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

you can use Regex expressions in Magic (Replace Text)

jaeW_at_Onyx · October 2020

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

you can use Regex expressions in Magic (Replace Text)

GrantSmith · October 2020

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:

Screen Shot 2020-10-22 at 11.26.28 AM.png

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 · October 2020

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

SQL DataFlows

SQL DataFlows

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

Welcome!

Best Answer

Answers

Welcome!

Welcome!

Quick Links

Categories