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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 simple way to remove dubbles from within a record
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
219 Posts

Posted - 04/05/2013 :  18:41:18  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi all,

I got a field with records that look like

john
james
james
eva
mike
chris
eva

how do i remove the doubles?


thanks a lot

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1834 Posts

Posted - 04/05/2013 :  19:43:13  Show Profile  Reply with Quote
I'm assuming that you don't have a primary key on the table; that the table consists of this single field. You could select the DISTINCT rows into another (temporary?/permanent?) table. Once that is complete you truncate the original table and then select the data back into the original. Alternately to the truncate/select, you could drop the original table and rename the new (permanent!) table to the original name.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 04/05/2013 :  23:20:19  Show Profile  Reply with Quote
create table #test (NM varchar(50));

-- truncate table #test

Insert into #test values ('john'),
('james'),
('james'),
('eva'),
('mike'),
('chris'),
('eva'),
('james');

SELECT distinct NM Into #temp
FROM #test
Begin tran
Truncate table #test

Insert Into #test
SELECT NM FROM #temp
Commit Tran
SELECT * from #test

Go to Top of Page

mike13
Posting Yak Master

Netherlands
219 Posts

Posted - 04/06/2013 :  04:08:38  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Hi,

I do got a autonumbering ID.
All the names are in one field, so not in different rows.
What i think needs to happen, but no idea how to do it.
-retrieve the record with values
-split them by line break
-insert into temp
-select distinct ones
-join them back into 1 record
-and update the record they where retrieved from

I think it is like this bu maybe I'm over complicating it ;-)

thanks a lot
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 04/06/2013 :  19:12:34  Show Profile  Reply with Quote
--drop table #test
--drop table #OriginalTable
--drop table #test2

create table #OriginalTable (ID int,NM varchar(max));

-- truncate table #test
DECLARE @CHR varchar(1)
SELECT @CHR = char(10) -- Character that separates names

Insert into #OriginalTable values (1,'john' + @CHR + 'james' + @CHR + 'eva' + @CHR + 'james' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james'),
(2,'john' + @CHR + 'bob' + @CHR + 'eva' + @CHR + 'james' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james')

-- Before
select * from #OriginalTable

SELECT * into #test
FROM #OriginalTable;

BEGIN Tran;
Truncate Table #OriginalTable;

WITH SRC as
(
select ID,substring(NM,1,charindex(@CHR,NM,1)) OneName, right(NM,Len(NM) - charindex(@CHR,NM,1)) as Rest
from #test
union all
SELECT #test.ID,CASE WHEN charindex(@CHR,Rest,1) <> 0 then substring(Rest,1,charindex(@CHR,Rest,1) - 1)
ELSE Rest end as OneName,
Case when charindex(@CHR,Rest,1) <> 0 then right(Rest,Len(Rest) - charindex(@CHR,Rest,1) )
else '' end as Rest
from SRC JOIN #test ON SRC.ID = #test.ID
WHERE Rest <> ''
)

SELECT DISTINCT ID,OneName INTO #test2
FROM SRC

INSERT INTO #OriginalTable
SELECT ID,REPLACE(Right(Names,len(Names) - 1),'/',@CHR)
FROM (
select ID,(
select '/' + OneName
from #test2
WHERE #test2.ID = a.ID
for XML Path('')) as Names
FROM #test2 a
GROUP BY ID) z
;
Commit Tran;

-- After
select * from #OriginalTable

Edited by - UnemployedInOz on 04/06/2013 19:14:03
Go to Top of Page

Prakash Machiraju
Starting Member

United Kingdom
2 Posts

Posted - 04/07/2013 :  07:06:25  Show Profile  Reply with Quote
There are a few different ways we can do this. Please check this blog post

http://blog.sqlauthority.com/2009/01/15/sql-server-remove-duplicate-entry-from-comma-delimited-string-udf/



______________________
Prakash Machiraju
Go to Top of Page

mike13
Posting Yak Master

Netherlands
219 Posts

Posted - 04/09/2013 :  17:08:55  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
Thanks unemployedinoz,

Was breaking my head to get it into my DB, i came up with this, which to test only does 1 row. of course it should do all rows

drop table #test2

DECLARE @CHR varchar(1)
SELECT @CHR = char(10)
BEGIN Tran;
WITH SRC as
(
SELECT ID,substring(Keyword,1,charindex(@CHR,Keyword,1)) OneName, right(Keyword,Len(Keyword) - charindex(@CHR,Keyword,1)) as Rest
from dbo.T_Keyword WHERE sitename='domain.com' AND List_name='level3'
union all
SELECT dbo.T_Keyword.ID,CASE WHEN charindex(@CHR,Rest,1) <> 0 then substring(Rest,1,charindex(@CHR,Rest,1) - 1)
ELSE Rest end as OneName,
Case when charindex(@CHR,Rest,1) <> 0 then right(Rest,Len(Rest) - charindex(@CHR,Rest,1) )
else '' end as Rest
from SRC JOIN dbo.T_Keyword ON SRC.ID = dbo.T_Keyword.ID
WHERE Rest <> ''
)
SELECT DISTINCT ID,OneName INTO #test2
FROM SRC

update T_Keyword
SET keyword=(SELECT REPLACE(Right(Names,len(Names) - 1),'/',@CHR)
FROM (
select ID,(
select '/' + OneName
from #test2
WHERE #test2.ID = a.ID
for XML Path('')) as Names
FROM #test2 a
GROUP BY ID) z) WHERE sitename='domain.com' AND List_name='level3'
;
Commit Tran;

select * from dbo.T_Keyword WHERE sitename='domain.com' AND List_name='level3'


But for some reason it doesn't seem to remove the duplicates.

Thanks a lot
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 04/09/2013 :  19:49:02  Show Profile  Reply with Quote

drop table #T_Keyword
drop table #test2
go
create table #T_Keyword (ID int,Keyword varchar(max),sitename varchar(50),List_name varchar(50));

-- truncate table #test
DECLARE @CHR varchar(1)
SELECT @CHR = char(10) -- Character that separates names

Insert into #T_Keyword values (1,'john' + @CHR + 'james' + @CHR + 'eva' + @CHR + 'james' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james','domain.com','level3'),
(2,'john' + @CHR + 'bob' + @CHR + 'eva' + @CHR + 'james' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james','domain.com','level3'),
(3,'john' + @CHR + 'bob2' + @CHR + 'ev3a' + @CHR + 'james' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james','diuhium','kpokpok'),
(4,'john' + @CHR + 'bob2' + @CHR + 'ev3a' + @CHR + 'james2' + @CHR + 'mike'+ @CHR +'chris' + @CHR + 'eva' + @CHR + 'james2','diuhium','kpokpok')


-- Before
select * from #T_Keyword


BEGIN Tran;
WITH SRC as
(
SELECT ID,substring(Keyword,1,charindex(@CHR,Keyword,1)) OneName, right(Keyword,Len(Keyword) - charindex(@CHR,Keyword,1)) as Rest
from #T_Keyword WHERE sitename='domain.com' AND List_name='level3'
union all
SELECT #T_Keyword.ID,CASE WHEN charindex(@CHR,Rest,1) <> 0 then substring(Rest,1,charindex(@CHR,Rest,1) - 1)
ELSE Rest end as OneName,
Case when charindex(@CHR,Rest,1) <> 0 then right(Rest,Len(Rest) - charindex(@CHR,Rest,1) )
else '' end as Rest
from SRC JOIN #T_Keyword ON SRC.ID = #T_Keyword.ID
WHERE Rest <> ''
)

SELECT DISTINCT ID,OneName INTO #test2
FROM SRC

Update #T_Keyword Set
#T_Keyword.Keyword = Chng
FROM #T_Keyword JOIN
(SELECT ID,REPLACE(Right(Names,len(Names) - 1),'/',@CHR) as Chng
FROM (
select ID,(
select '/' + OneName
from #test2
WHERE #test2.ID = a.ID
for XML Path('')) as Names
FROM #test2 a
GROUP BY ID) z ) y
ON y.ID = #T_Keyword.ID
;
Commit Tran;

select * from #T_Keyword WHERE sitename='domain.com' AND List_name='level3'

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000