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

DomoNewb ⚪️
edited March 14 in SQL DataFlows

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
    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"


  • 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