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)"
<[email protected]>
03/07/2008 10:14 AM
Please respond to
[email protected]
To
[email protected]
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)"
<[email protected]>
03/07/2008 06:34 AM
Please respond to
[email protected]
To
[email protected]
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"
