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

Find more posts tagged with

Accepted answers

ST_Superman

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

All comments

ST_Superman

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

n8isjack

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.

MattN

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}).*$)^.*$

MattN

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])).*$)^.*$

Domofied

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

jaeW_at_Onyx

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?

https://www.youtube.com/watch?v=oYcpYE7DiV4

GrantSmith

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