Or you can do this in the database. Here is an example doing it in Oracle.
------------
with
data
as
(
select token, rownum rn
from (
select distinct
trim( substr (txt,
instr (txt, ',', 1, level ) + 1,
instr (txt, ',', 1, level+1)
- instr (txt, ',', 1, level) -1 ) )
as token
from (select ','||ltrim(rtrim('[EMAIL PROTECTED],
[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL
PROTECTED]',','),',')||',' txt
from dual)
connect by level <=
length(txt)-length(replace(txt,',',''))-1
)
)
select ltrim( max( sys_connect_by_path(token,',') ), ',' ) || ',' str
from data
start with rn = 1
connect by prior rn = rn-1
------------
(http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15258974323143)
--
Jarl
On Fri, Mar 7, 2008 at 3:34 PM, Darshana Jivan [MTN Network Solutions]
<[EMAIL PROTECTED]> wrote:
> **
>
>
>
> 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___
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
