Check for duplicates using a csv list [Solved]

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check for duplicates using a csv list [Solved]

Author

Topic

GlynD02
Starting Member

13 Posts

Posted - 2008-09-01 : 10:26:48

Hi there I was wondering if anyone could help with the following please?

I have a table that contains a column of email address and a separate comma separated list of email address. How do I check that the list of emails are not already in the table please? (I don't need to delete them from the DB just return any duplicates...)

Comma sep list: "user5@email.com, user6@email.com, etc"

Table EmailTable
EmailAddress column: user2@email.com, user3@email.com, user5@email.com etc...

Without having to do the following:

Select EmailAddress
From EmailTable
Where EmailAddress = 'user5@email.com'
OR EmailAddress = 'user6@email.com'
etc, etc...

Many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 10:31:57

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.63"
N 56°04'39.26"

GlynD02
Starting Member

13 Posts

Posted - 2008-09-01 : 10:33:50

Peso

It is SQL Server 2000...

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 10:36:22

There is several solutions to this dilemma.
The most obvious one is to make your table normalized.
See http://www.datamodel.org/NormalizationRules.html

E 12°55'05.63"
N 56°04'39.26"

GlynD02
Starting Member

13 Posts

Posted - 2008-09-01 : 10:45:44

Thanks for that, the problem is this is a database and VB app not built by me and to rebuild both would take a significant amount of time and VB experience that I do not have :-(

The VB app takes in a CSV list and sends the newsletter email to our customers. Problem is the boss is giving me separate lists of CSVs address. Whereas before I got a single list and was able to check it for duplicates in it's CSV form... I now need to check them against the DB before I bring them into the VB app!

Cheers

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 11:22:17

Why not create a new table with unique constraint on the column and enter all subscription emails there.

GlynD02
Starting Member

13 Posts

Posted - 2008-09-01 : 11:29:34

Thanks afrika

Because I have to remove the duplicates out of the CSV list before I can use the CSV list in the VB app...

Cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-01 : 11:33:56

[code]-- Prepare user input
DECLARE @Table TABLE
(
eMail VARCHAR(80)
)

DECLARE @csv VARCHAR(1000)

INSERT @Table
SELECT 'user2@email.com' UNION ALL
SELECT 'user3@email.com' UNION ALL
SELECT 'user5@email.com'

SET @csv = 'user5@email.com, user6@email.com'

SELECT @csv
SELECT eMail
FROM @Table

-- Stage user input
SET @csv = ',' + REPLACE(@csv, ' ', '') + ','

-- Find out which email already exists
SELECT t.eMail
FROM @Table AS t
WHERE @csv LIKE '%,' + t.eMail + ',%'[/code]

E 12°55'05.63"
N 56°04'39.26"

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 11:54:01

quote:
Originally posted by GlynD02

Thanks afrika

Because I have to remove the duplicates out of the CSV list before I can use the CSV list in the VB app...

Cheers

Very simple, the unique constraint will through out the duplicates and simplify things for you

GlynD02
Starting Member

13 Posts

Posted - 2008-09-01 : 12:19:32

Thank you very much Peso that did the trick!

Afrika thanks for the advice as well...

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources