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!


Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤
    Answer ✓


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

    you can use Regex expressions in Magic (Replace Text)



    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"


  • 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)

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**
  • Awesome, it sounds like that will work for me! Thanks