Substring of Email Address

How-to(s), solutions to common problems, pearls of wisdom

hesske
Posts: 7
Joined: Wed Jan 03, 2018 7:31 pm

Substring of Email Address

Postby hesske » Wed Jan 03, 2018 7:58 pm

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
Posts: 7
Joined: Wed Jan 03, 2018 7:31 pm

Re: Substring of Email Address

Postby hesske » Thu Jan 04, 2018 3:36 pm

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
Posts: 7
Joined: Wed Jan 03, 2018 7:31 pm

Re: Substring of Email Address

Postby hesske » Thu Jan 04, 2018 6:58 pm

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.

hesske
Posts: 7
Joined: Wed Jan 03, 2018 7:31 pm

Re: Substring of Email Address

Postby hesske » Thu Jan 04, 2018 10:19 pm

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+)", " "

cholastal
Posts: 111
Joined: Tue Sep 01, 2015 1:22 pm

Re: Substring of Email Address

Postby cholastal » Mon Jan 08, 2018 4:46 pm

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,
Attachments
Substring_of_Email_Address.grf
(3.34 KiB) Downloaded 42 times
---
Lukas Cholasta
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

the_goat
Posts: 21
Joined: Fri Dec 01, 2017 6:16 am

Re: Substring of Email Address

Postby the_goat » Tue Jan 09, 2018 10:39 am

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));

the_goat
Posts: 21
Joined: Fri Dec 01, 2017 6:16 am

Re: Substring of Email Address

Postby the_goat » Wed Jan 10, 2018 9:33 am

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
Posts: 7
Joined: Wed Jan 03, 2018 7:31 pm

Re: Substring of Email Address

Postby hesske » Fri Feb 02, 2018 5:31 pm

Thanks everyone for the good input. :)