Using RegEx to split a column at a delimeter

Laf8991
Laf8991 Member
in Magic ETL

I currently am working with a column that is delimited using underscores. I was able to write the following RegEx in a builder which was able to do what I needed: ^(?:[^_]+)

What is the best way to implement this RegEx to return the part of the string that this captures?

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    Have you looked at the Split Column tile in Magic ETL?

    Screen Shot 2023-08-18 at 1.31.55 PM.png

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile and the REGEXP_REPLACE function to get just the value of your match. There isn't a regex substring function so you essentially have to match the entire string and then replace it with your value:

    REGEXP_REPLACE(`field`, '^(?:[^_]+).*$', '$1')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓

    Have you looked at the Split Column tile in Magic ETL?

    Screen Shot 2023-08-18 at 1.31.55 PM.png

    If I solved your problem, please select "yes" above

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can use a formula tile and the REGEXP_REPLACE function to get just the value of your match. There isn't a regex substring function so you essentially have to match the entire string and then replace it with your value:

    REGEXP_REPLACE(`field`, '^(?:[^_]+).*$', '$1')
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories