SQL Server Forums
Profile | Register | 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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 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
1693 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
214 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
214 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  
 New 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