Page 1 of 1

Substring of Email Address

Posted: Wed Jan 03, 2018 7:58 pm
by hesske
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

Re: Substring of Email Address

Posted: Thu Jan 04, 2018 3:36 pm
by hesske
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.

Re: Substring of Email Address

Posted: Thu Jan 04, 2018 6:58 pm
by hesske
hesske wrote: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.

Re: Substring of Email Address

Posted: Thu Jan 04, 2018 10:19 pm
by hesske
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+)", " "

Re: Substring of Email Address

Posted: Mon Jan 08, 2018 4:46 pm
by cholastal
Hi hesske,

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

Code: Select all

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


Attached is an example graph.

Best regards,

Re: Substring of Email Address

Posted: Tue Jan 09, 2018 10:39 am
by the_goat
Well, there are many ways. Best performing might be your "excel" way:

Code: Select all

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


Lazy way might be what Lukas proposed:

Code: Select all

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


Or similarly:

Code: Select all

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

Re: Substring of Email Address

Posted: Wed Jan 10, 2018 9:33 am
by the_goat
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 :(

Re: Substring of Email Address

Posted: Fri Feb 02, 2018 5:31 pm
by hesske
Thanks everyone for the good input. :)