Here is my little test to simulate what you have and remove duplicates. I worked with first 14 characters just to make it small.
-
1. Create a table
-
create table #a (name varchar(100))
-
2. Insert some records
-
insert into #a values ('James d. Smith has deployed')
-
insert into #a values ('James d. Smith has deployed')
-
insert into #a values ('b James d. Smith')
-
insert into #a values ('a James d. Smith has deployed')
-
insert into #a values ('James d. Smith has deployed')
-
insert into #a values ('James d. Smith has deployed')
-
insert into #a values ('b James d. Smith')
-
insert into #a values ('a James d. Smith has deployed')
-
-
3. Run code:
-
set rowcount 1
-
-
select 1
-
While @@Rowcount > 0
-
BEGIN
-
delete from #a where substring(name,1,14) = (select top 1 substring(name,1,14)
-
from #a
-
group by substring(name,1,14)
-
having count(*) > 1)
-
END
-
set rowcount 0
-
-
4. Check result
-
select * from #a order by 1
Your result should be:
a James d. Smith has deployed
b James d. Smith
James d. Smith has deployed
Then you can add a column to the table with first 100 characters to be able to have unique records.
Good Luck.
