quote:

---

Originally posted by blindman

So if your list includes "Lord Of The Rings" and "The Graduate", you want to count them as the same movie because they each contain the word "the"?

You may want to rethink this...

---

quote:

---

Originally posted by blindman

So if your list includes "Lord Of The Rings" and "The Graduate", you want to count them as the same movie because they each contain the word "the"?

You may want to rethink this...

---

quote:

---

Originally posted by Srinika

yalgaar,
My Question again (for clarification)
can u have a situation like
"The Titanic" and "The Ship Titanic is a" where u wan to have duplicate as "Titanic" ?

Ur search may take a long time if the field of each record contains lot of words (even if u eliminate some words as "the") - because the field value needs to be broken to each word and those needs to be checked against each field whether those exists or not

eg.
Let the Feild contains following 3 records
Rec1 : ABCD PQR ST UVW XYZ
Rec2 : LMN OPQ ST U XYZ
Rec3 : BCD LMN PQR ST

Rec1's each word, ie ABCD PQR ST UVW XYZ should be checked against Rec2 & Rec3
So for these 3 records the iteration is done 5 x 2 + 5 x 2 + 4 x 2
U will get results as
Rec1 & Rec2 "Duplicating - as per ur definition" 2 times (with regard to data items ST & XYZ)
Rec1 & Rec3 "Duplicating - as per ur definition" 2 times (with regard to data items ST & PQR)
Rec2 & Rec3 "Duplicating - as per ur definition" 2 times (with regard to data items ST & LMN)

Is that what u want?

---

quote:

---

Originally posted by Billkamm

blindman: no I wouldn't. I would just create a loop that caused a timed delay :)

---

drop function CompareText
go
create function CompareText(@String1 varchar(100), @String2 varchar(100))
returns int
as
begin
--Function CompareText
--blindman, 2005
--Compares two strings and returns a value between 0 and 100 indicating a measure of their
--similarity.

--Test variables
-- declare	@String1 varchar(100)
-- declare	@String2 varchar(100)
-- set	@String1 = 'Bruce Alan Lindman'
-- set	@String2 = 'Lindman, Bruce A'
-- set	@String1 = dbo.MatchText(@String1)
-- set	@String2 = dbo.MatchText(@String2)

declare	@Possibles int
declare @Hits int
declare @Counter int
set	@Possibles = len(@String1) + len(@String2) - 2
set	@Hits = 0
set	@Counter = len(@String1)-1
while	@Counter > 0
	begin
	if charindex(substring(@String1, @Counter, 2), @String2) > 0 set @Hits = @Hits + 1
	set	@Counter = @Counter - 1
	end
set	@Counter = len(@String2)-1
while	@Counter > 0
	begin
	if charindex(substring(@String2, @Counter, 2), @String1) > 0 set @Hits = @Hits + 1
	set	@Counter = @Counter - 1
	end
-- select	cast(100.0*@Hits/@Possibles as int)
return	cast(100.0*@Hits/@Possibles as int)
end

set nocount on

declare	@MovieTitles table (MovieTitle varchar(50))

insert into @MovieTitles (MovieTitle)
select	'The Titanic'
UNION
select	'Titanic'
UNION
select	'Bruce Allmighty'
UNION
select	'ABCD'
UNION
select	'Cheaper by the Dozen'
UNION
select	'Dozen'
UNION
select	'The Dirty Dozen'
UNION
select	'Meet the Parents'
UNION
select	'Meat the Parents'
UNION
select	'Mary Poppins'
UNION
select	'There''s something about Mary'


select	A.MovieTitle MovieTitle,
	B.MovieTitle DuplicateTitle,
	dbo.CompareText(A.MovieTitle, B.MovieTitle) MatchValue
from	@MovieTitles A
	inner join @MovieTitles B on A.MovieTitle < B.MovieTitle
where	dbo.CompareText(A.MovieTitle, B.MovieTitle) > 50
order by A.MovieTitle asc,
	dbo.CompareText(A.MovieTitle, B.MovieTitle) desc

MovieTitle                                         DuplicateTitle                                     MatchValue  
-------------------------------------------------- -------------------------------------------------- ----------- 
Cheaper by the Dozen                               The Dirty Dozen                                    60
Meat the Parents                                   Meet the Parents                                   86
The Titanic                                        Titanic                                            75