Pulling String Before & After Characters

Options
Mwatson
Mwatson Member
in Magic ETL

I'm trying to trim email addresses after the @ and before the . So for example if your email is joesmith@domo.com. I just want to return "domo." I'm using the formula tile in Magic ETL.

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited August 2023 Answer ✓
    Options

    Using the Replace Text tile, do the following:

    Screen Shot 2023-08-03 at 4.24.57 PM.png

    The 2nd one is a bit hard to read in the screenshot, it is: \ . . *

    Be sure to check "use regex" for both as shown below:

    Screen Shot 2023-08-03 at 4.25.26 PM.png

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

  • GrantSmith
    GrantSmith Coach
    edited August 2023 Answer ✓
    Options
     

    Using a formula tile you can use a regexp_replace function to extract just the domain:

    REGEXP_REPLACE(`email`, '^[^@]+@([^\.]+)\..*$', '$1')

    ^ - match the beginning of the string
    [^@]+ - Match 1 or more (+) of characters that are NOT (^) the @ symbol.
    @ - match the literal @ symbol
    [^\.]+
    () - Store this for later reference in a match variable. Since this is the first instance it's stored in $1
    [^\.]+ - Match 1 or more (+) of characters that are NOT (^) the . symbol. \ is to escape the . character as it has special meaning in regular expressions of any character.
    \. - match the literal . character
    .* - match 0 or more (*) of any character (.)
    $ - match the end of the string
    $1 - Where the value between the parenthesis are stored.
     
    

    **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 ✓ 
    

     
    
Options
       
     
    

    

    

    

    

    Using the Replace Text tile, do the following:
    

    

Screen Shot 2023-08-03 at 4.24.57 PM.png

    

    

    The 2nd one is a bit hard to read in the screenshot, it is: \ . . *

    Be sure to check "use regex" for both as shown below: 
    

    

Screen Shot 2023-08-03 at 4.25.26 PM.png

    

    

     
    

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

    

    

    • 

  • 

    

    
GrantSmith 
     
    

GrantSmith 

Coach  

    

    

 
 edited August 2023  Answer ✓ 
    

     
    
Options
       
     
    

    

    

    

    

    Using a formula tile you can use a regexp_replace function to extract just the domain:

    REGEXP_REPLACE(`email`, '^[^@]+@([^\.]+)\..*$', '$1')

    ^ - match the beginning of the string
    [^@]+ - Match 1 or more (+) of characters that are NOT (^) the @ symbol.
    @ - match the literal @ symbol
    [^\.]+
    () - Store this for later reference in a match variable. Since this is the first instance it's stored in $1
    [^\.]+ - Match 1 or more (+) of characters that are NOT (^) the . symbol. \ is to escape the . character as it has special meaning in regular expressions of any character.
    \. - match the literal . character
    .* - match 0 or more (*) of any character (.)
    $ - match the end of the string
    $1 - Where the value between the parenthesis are stored.
     
    

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

    

    

    • 



 








 
 


Categories