Magic Replace Text REGex, remove anything outside of delimiters

StuB
StuB Member
in Magic ETL

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

  • ST_Superman
    ST_Superman Domo Employee
    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

Answers

  • ST_Superman
    ST_Superman Domo Employee
    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

  • n8isjack
    n8isjack Contributor

    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"
  • MattN
    MattN Member

    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
    MattN Member

    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
    Domofied Contributor

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

    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


    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 @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!**

Categories