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
-
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"0
Answers
-
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"0 -
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:
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!**0 -
Awesome, it sounds like that will work for me! Thanks
0
Categories
- All Categories
- 1.5K Product Ideas
- 1.5K Ideas Exchange
- 1.4K Connect
- 1.1K Connectors
- 283 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.7K Transform
- 90 SQL DataFlows
- 565 Datasets
- 2K Magic ETL
- 3.4K Visualize
- 2.3K Charting
- 593 Beast Mode
- 13 App Studio
- 28 Variables
- 588 Automate
- 143 Apps
- 417 APIs & Domo Developer
- 27 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 361 Distribute
- 99 Domo Everywhere
- 260 Scheduled Reports
- 2 Software Integrations
- 96 Manage
- 93 Governance & Security
- 15 Product Releases
- Community Forums
- 37 Getting Started
- 28 Community Member Introductions
- 90 Community Announcements
- 4.8K Archive