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

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!

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Accepted Answer

     

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

    you can use Regex expressions in Magic (Replace Text)

     

     

Answers

  • GrantSmith
    GrantSmith Indiana 🟤

    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)
  • Awesome, it sounds like that will work for me! Thanks

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!