Solution to search for duplicates in TSQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Solution to search for duplicates in TSQL?

Author

Topic

lopes_andre
Starting Member

12 Posts

Posted - 2010-01-25 : 10:30:47

Hi,

I'am in trouble with a Procedure in TSQL.

My problem is the following. In the procedure I generate s string with e-mails, but sometimes the string contains duplicate e-mails. Tje string looks like this:

email_1@mail.com;email_2@mail.com;email_1@mail.com;email_1@mail.com

As you can see there is duplicate e-mails in this string. Can someone give me a clue on how to remove the duplicates from this string?

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:38:00

use distinct. thats what i can say based on info provided.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-01-25 : 10:38:54

sort this out in your incoming data in the first place!
the horse has bolted by the time it's in this format.

select mycol, count(*) from mytable
group by mycol
having count(*) > 1

will identify duplicates.

read the FAQ's for advice on deleting duplicates.

lopes_andre
Starting Member

12 Posts

Posted - 2010-01-25 : 10:45:28

Hi,

The emails are in a string, they are not in a table.

I was thinking create an array to put the e-mails and then make the SELECT DISTINCT. This is possible?

Sorry my bad english.

Best Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 10:47:33

quote:
Originally posted by lopes_andre

Hi,

The emails are in a string, they are not in a table.

I was thinking create an array to put the e-mails and then make the SELECT DISTINCT. This is possible?

Sorry my bad english.

Best Regards,

how is string generated?

lopes_andre
Starting Member

12 Posts

Posted - 2010-01-25 : 11:01:30

Hi,

The string is generated by "concatenation". I don't want to rewrite the code before of the generation of the string.

I have tested this solution, I can send the e-mail list to a array table, like this:


--Create Table Variable
Declare @t table
(names varchar(2000))

--Prepare sample data
insert @t
Select 'email_1@mail.com' union all
Select 'email_2@mail.com' union all
Select 'email_1@mail.com'

select distinct * from @t

Now, how can I INSERT the values from my string, separated by ";"?

My string is: email_1@mail.com;email_2@mail.com;email_1@mail.com;email_1@mail.com

Best Regards.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 11:08:33

[code]select stuff((select ';' + names
from
(
select distinct names from @t
)t
for xml path('')),1,1,'')
[/code]

lopes_andre
Starting Member

12 Posts

Posted - 2010-01-25 : 11:28:50

Thanks for the reply visakh16,

I will need that, but before I need to INSERT to the @t table the values from the string(email_1@mail.com;email_2@mail.com;email_1@mail.com). How can I insert this values separated by ";" to the table @t?

Best Regards,

jconway
Starting Member

2 Posts

Posted - 2010-01-25 : 12:24:19

You could also try something like this:

declare @emails varchar(max), @loc int
set @emails = 'email1@mail.com;email2@mail.com;email3@mail.com;email1@mail.com;email1@mail.com;email3@mail.com'
set @loc = 1
while charindex(';',@emails,@loc) > 0 begin
set @emails = substring(@emails,1,charindex(';',@emails,@loc)-1)
+ replace(substring(@emails,charindex(';',@emails,@loc),len(@emails)),';'+substring(@emails,@loc,charindex(';',@emails,@loc)-@loc),'')
set @loc = charindex(';',@emails,@loc) + 1
end
print @emails

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 23:59:40

quote:
Originally posted by lopes_andre

Thanks for the reply visakh16,

I will need that, but before I need to INSERT to the @t table the values from the string(email_1@mail.com;email_2@mail.com;email_1@mail.com). How can I insert this values separated by ";" to the table @t?

Best Regards,

you mean you want to get string values parsed and inserted as individual row values in table?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources