simple way to remove dubbles from within a record

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 2012 Forums

Transact-SQL (2012)

simple way to remove dubbles from within a record

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-05 : 18:41:18

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
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-04-05 : 19:43:13

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-04-05 : 23:20:19

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

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-06 : 04:08:38

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-04-06 : 19:12:34

--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

Prakash Machiraju
Starting Member

2 Posts

Posted - 2013-04-07 : 07:06:25

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

mike13
Posting Yak Master

219 Posts

Posted - 2013-04-09 : 17:08:55

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-04-09 : 19:49:02

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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources