Darshana, I just realized I didn't actually answer your question. Your "Set Fields 3": Set Fields 3: REPLACE($MTNNS_primary_client_contact_email$, SUBSTR($MTNNS_primary_client_contact_email$, $Val1$, $Val2$), "")
doesn't work because $Val2$ is storing the length of the email address, not the end position in the string. If you change that to: REPLACE($MTNNS_primary_client_contact_email$, SUBSTR($MTNNS_primary_client_contact_email$, $Val1$, $Val1$ + $Val2$), "") You'll get closer. Or leave your set fields 3 the same, and change set fields 2: Set Fields 2: Val2 = $Val1$ + LENGTH($Email$) 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], [EMAIL PROTECTED], [EMAIL PROTECTED] the duplicate occurrence should be extracted from the string. [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [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.*** _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"