Reg Ex difficulty

renee12345
renee12345 Member
in Magic ETL

I am attempting to pull out a string using Reg ex.

I want to get the card name, so text after the --- card_name: and between measurable_id.

I am using .*measurable_id) but it is giving me an error message?

Screenshot 2023-01-25 at 9.29.13 AM.png


Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Sorry, forgot to have you pass in the 'm' and 'n' flags to the regexp_replace function to tell it to match newline characters with the . reference.

    m: Multiple-line mode.

    n: The . character matches line terminators.

    REGEXP_REPLACE(`text`, '^.*card_name: (.*)\nmeasureable_id:.*', '$1', 'mn')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Magic ETL formula tile should allow this:

    REGEXP_REPLACE(`alert.message`, '^.*card_name: (.*) measurable_id: .*$', '$1')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • renee12345
    renee12345 Member

    Hi @GrantSmith thanks for the quick response.


    It doesn't seem to be working on my end, the result looks the same as the column values :/

  • renee12345
    renee12345 Member

    Does formatting affect regexp_replace?

    This is how I'm seeing it exported:

    Screenshot 2023-01-25 at 9.53.47 AM.png


  • GrantSmith
    GrantSmith Coach
    edited January 2023

    Yes. That would affect the regular expression as there's a newline character prior to your measure_id.

    Try this version:

    REGEXP_REPLACE(`alert.message`, '^.*card_name: (.*)\nmeasurable_id: .*$', '$1')


    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • renee12345
    renee12345 Member

    Unfortunately that version didn't work either :/


    Is there another way around using the Replace Text tile?

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Sorry, forgot to have you pass in the 'm' and 'n' flags to the regexp_replace function to tell it to match newline characters with the . reference.

    m: Multiple-line mode.

    n: The . character matches line terminators.

    REGEXP_REPLACE(`text`, '^.*card_name: (.*)\nmeasureable_id:.*', '$1', 'mn')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories