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

Options
DomoNewb
DomoNewb Member
edited March 2023 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!

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"
  • GrantSmith
    GrantSmith Coach

    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!**
  • DomoNewb
    DomoNewb Member

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

Categories