Substring of Email Address

hesske

• January 03, 2018 00:00

• Answered

Follow

I hope I haven't overlooked this in the Help section or here on the forum, but I just need a function to get the characters to the left of the "@" in the email address column of my metadata to use as an username in my output flat file. In Excel, you can just use =LEFT(email,FIND("@",email)-1). Is there a similar way to do this in CloverETL? The problem is that the name part isn't always the same length, so I can't just use the index and length, so I need everything to the left of the '@'.
Thanks

• 

  hesske

  • January 04, 2018 14:36

  0

  Comment actions Permalink

  I think I have found a way to do it via the replace expression. The one issue I've run into is there are a few cases where the domain is different than most of the email addresses. Trying to find a way to use a variable after the @ to get all characters after the @ sign.
• 

  hesske

  • January 04, 2018 17:58

  0

  Comment actions Permalink

  Trying to find a way to use a variable after the @ to get all characters after the @ sign.

  
  
  Sorry, not a variable, but a wildcard to use.
• 

  hesske

  • January 04, 2018 21:19

  0

  Comment actions Permalink

  Just wanted to let everyone know I found a regex that accomplished what I needed. Here is my code:
  replace($in.0.EMAIL_ADDR, "@(\\w+.\\w+)", " "
• 

  Lukas Cholasta

  • January 08, 2018 15:46

  0

  Comment actions Permalink

  Hi hesske,
  
  You can accomplish this multiple ways. The most convenient seems to be to use split function. Please take this an example.
  
  

  string[] out = split($in.0.address, "@");

  
  
  Attached is an example graph.
  
  Best regards,

  • Substring_of_Email_Address.grf
• 

  the_goat

  • January 09, 2018 09:39

  0

  Comment actions Permalink

  Well, there are many ways. Best performing might be your "excel" way:
  

  addr = left($in.0.email,indexOf($in.0.email,"@")-1);

  
  
  Lazy way might be what Lukas proposed:
  

  addr = pop(split($in.0.email,"@"));

  
  
  Or similarly:
  

  addr = pop(find($in.0.email,"^([^@]+)",1));

• 

  the_goat

  • January 10, 2018 08:33

  0

  Comment actions Permalink

  Oh, sorry. Instead of pop(), the function should be poll(), you want an element from beginning of list; not the end. Cannot edit the original post tho :(
• 

  hesske

  • February 02, 2018 16:31

  0

  Comment actions Permalink

  Thanks everyone for the good input. :)

Please sign in to leave a comment.