Magic Replace Text REGex, remove anything outside of delimiters
Hello!
I am trying to use the regex replace text in magic and am having difficulty getting the desired result. I need to capture a string between two delimiters and delete everything else.
An example being:
"EM|CX-001|Test Campaign Name" and grabbing only "CX-001". I cannot use a substring as the number of characters before the pipe and after the pipe may change.
I tried using the regex (?<=\|)(.*?)(?=\-), but while this selects CX-001, the replace text in magic would remove it instead of removing everything else.
Any regex people able to help?
Thanks
Best Answer
-
You need to change your thinking slightly. Try splitting it up into two steps. First grab everything up to and including the first pipe, then replace with ''
then grab everything from the second pipe on and replace with ''
what will be left will be the desired value between the pipes
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0
Answers
-
You need to change your thinking slightly. Try splitting it up into two steps. First grab everything up to and including the first pipe, then replace with ''
then grab everything from the second pipe on and replace with ''
what will be left will be the desired value between the pipes
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman0 -
You could possibly do it in one step:
REGEX: ^.*\|(.*)\|.*$
REPLACE: $1
The trick is to make sure your pattern matches the ENTIRE string. Use parenthesis to capture the part you want to keep and when you replace with the captured portion it will replace the entire matched string (which is the whole field) with the captured portion.
If your string does not conform to the pattern it will go untouched. In this case the only required characters are two pipes. If you have a string without those pipes there will be no change and it will pass through.
-----------------
Just as an FYI here is how I would put in a safety catch for records without two pipes in them. In the Replace Text action I would add a step BEFORE the above approach.
Pre-Regex Catch-all: Intended regex does not match the text in the field
REGEX: (?!^.*\|(.*)\|.*$)^.*$
REPLACE: <blank>
This is the exact same pattern but in a negative lookahead, then ^.*$ to match anything in the field and replace the whole field with blank. You don't want this after the real one or it will wipe everything out. It has to run first.
**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"1 -
Thank you for adding this piece, I'm doing a similar statement to extract dates, by using the negated version first fixed my issues as not all strings had a date in them. (?!^.*(\d{4}-\d{1,2}-\d{1,2}).*$)^.*$
0 -
Actually I had to use this in the negated version to get only valid dates, was getting some numbers that were not dates and couldn't convert to date format further down the flow.
(?!^.*(((?:19|20)\d\d)-(0?[1-9]|1[012])-([12][0-9]|3[01]|0?[1-9])).*$)^.*$
0 -
Hi Everyone,
Does your formulas work on multi-line text ?
I'm using Regex101 website and the formula I have works on multi-lines but not in Domo.
^(?!Numbers:).*
This removes all previous lines before and after Numbers:
Here is the text the formula is filtering below, I hope its something small I missed and thank you in advance.
______________________________ OVERVIEW ______________________________
Booking Error: DangerousGoodContact 1.67.10 - Unable to add an item that has dangerous goods information without adding a dangerous goods contact name and phone number.
Carrier: Transport Express - Parcel
Account Number: TRS74844
Site: Solid Items - VIC
45 Yellow Court, Bentleigh East VIC 3168
Phone: -
User: Sales
Email: sales@soliditems.com.au
Phone: -
Consignments: 5A
Numbers: SOSO101951 SOSO101954 SOSO101952 SOSO101953 SOSO101955
Largest Dimensions: 26x49x32 cm
Total Weight: 49.0 kg
Total Cubic: 0.1623 m3
Total Items: 5
Dangerous Goods: YES
0 -
if i had to solve this problem, i would ask myself:
1) can i find the position of thte text "Numbers: "
2) can i find the first New line character after the position ofthe text Numbers:
3) can i keep the stuff in between?
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"1 -
Hi @Domofied
If you have Magic ETL 2.0 you can use the regex_replace function to pass in processing flags - this isn't possible with the ETL 1.0 Replace Text tile. By default regex101 as the g (global) and m (multiline) flags enabled whereas Domo does not. Passing in the 'm' as the third parameter should resolve your issue.
REGEXP_REPLACE(`note`, '^(?!Numbers:).*', '$1', 'm')
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive