Thanks Thad.
This solution worked. Tell me if I want to use the same functionality but on cell phone numbers how do I do this? what would I change the LOWER function too? Regards, Darshana From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Thad K Esser Sent: Friday, March 07, 2008 8:59 PM To: arslist@ARSLIST.ORG Subject: Re: Remove duplicate email address from a String ** Sorry folks, OCD is kicking in. :-) Darshana, If you were to get the same email address for three or more iterations in a row, you'd end up with triple commas, which isn't accounted for in my original post. Its probably best to clean up the double commas as you go, thereby ensuring we never get more than two in a row. So, in your AL that does the looping: Set Fields, SQL: SELECT CONTACT_EMAIL FROM [EMAIL PROTECTED] WHERE CONTACT_ID = '$Col_C_ContactID$' Set MTNNS_primary_client_contact_email = REPLACE(REPLACE($MTNNS_primary_client_contact_email$, LOWER($1$),"") + "," + LOWER($1$) , ",,",",") You'll still need to clip the leading comma after the loop is complete. Thad Esser Remedy Developer "Argue for your limitations, and sure enough, they're yours."-- Richard Bach "Thad K Esser" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 03/07/2008 10:14 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Re: Remove duplicate email address from a String ** Darshana, Along the lines of Frank's suggestion, how about the following: In your AL that does the looping: Set Fields, SQL: SELECT CONTACT_EMAIL FROM [EMAIL PROTECTED] WHERE CONTACT_ID = '$Col_C_ContactID$' MTNNS_primary_client_contact_email = REPLACE($MTNNS_primary_client_contact_email$, LOWER($1$),"") + "," + LOWER($1$) This will replace the email address with a null if its there (it does nothing if its not) and then adds it to the end. The LOWER() is just there in case your email addresses have mixed cases. You can use $1$ without storing it in a temp field ("Email" in your example), unless you need it for some other purpose. It also gets rid of the $Val1$ and $Val2$ temp fields. Then, once your loop is complete, do one more set fields to clean up any duplicate commas: MTNNS_primary_client_contact_email = REPLACE($MTNNS_primary_client_contact_email$, ",,",",") Oh, and unless you are handling it somewhere else, your code will cause the list to start with a comma (so will the above). You'll want to clip that off: MTNNS_primary_client_contact_email = SUBSTR($MTNNS_primary_client_contact_email$, 1) (substrings are zero-indexed, so starting at 1 skips the first char, which we know is a comma, and without the third parameter it'll return the rest of the string) For that matter, combine the last two set fields into one: MTNNS_primary_client_contact_email = SUBSTR(REPLACE($MTNNS_primary_client_contact_email$, ",,",",") , 1) I hope that helps. Thad Esser Remedy Developer "Argue for your limitations, and sure enough, they're yours."-- Richard Bach "Darshana Jivan [MTN Network Solutions]" <[EMAIL PROTECTED]> Sent by: "Action Request System discussion list(ARSList)" <arslist@ARSLIST.ORG> 03/07/2008 06:34 AM Please respond to arslist@ARSLIST.ORG To arslist@ARSLIST.ORG cc Subject Remove duplicate email address from a String ** Hi, Please can someone assist me with the following. I want to remove duplicate email addresses in a string. I cannot create a view with the distinct values as I need the unique id's of the record therefore the only option I do have is to try and remove duplicate occurrences after the 1st occurrence of an email address. This is the following process that I am using but I know that the last set fields with the replace function is not doing what I require it to do and there is where I need some assistance. I have a AL that fetches and email address and concatenates it to a string in a loop. SQL Command: SELECT CONTACT_EMAIL FROM [EMAIL PROTECTED] WHERE CONTACT_ID = '$Col_C_ContactID$' Set Fields 1: Email $1$ Set Fields 2: MTNNS_primary_client_contact_email ($MTNNS_primary_client_contact_email$ + ",") + $Email$ I then have another AL that does the following when it finds a match of an email address in the string 'MTNNS_primary_client_contact_email' Set Fields 1: Val1 STRSTR($MTNNS_primary_client_contact_email$, $Email$) Set Fields 2: Val2 LENGTH($Email$) Set Fields 3: REPLACE($MTNNS_primary_client_contact_email$, SUBSTR($MTNNS_primary_client_contact_email$, $Val1$, $Val2$), "") In essence if I have a table with the following email addresses and when its looped through the string or the value I am looking for is [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> , [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> , [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> . the duplicate occurrence should be extracted from the string. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.*** __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ ***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.*** ***IMPORTANT NOTICE: This communication, including any attachment, contains information that may be confidential or privileged, and is intended solely for the entity or individual to whom it is addressed. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message is strictly prohibited. Nothing in this email, including any attachment, is intended to be a legally binding signature.*** __Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are" html___ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
