Slackr,
You may create a word matching function as below and use the count of words matching to join the tables.
alter function Matching_Words_Count(@s1 varchar(1000), @s2 varchar(1000))
Returns int
Begin
Declare @word varchar(50)
Declare @ctr int, @match int
Select @s1 = Ltrim(Rtrim(@s1)), @s2 = Ltrim(Rtrim(@s2)), @word = '', @match=0, @ctr=1
While @ctr<=len(@s1)
Begin
--Check for space
If(Substring(@s1,@ctr,1)=char(32))
Begin
While(Substring(@s1,@ctr,1)=char(32)) Set @ctr = @ctr + 1
If @word NOT IN ('the','inc.,','ltd','co.,','of')
AND charindex(@word,@s2)> 0
Set @match=@match+1
Set @word = ''
End
Set @word = @word + Substring(@s1,@ctr,1)
Set @ctr = @ctr + 1
End
If @word NOT IN ('the','inc.,','ltd','co.,','of') If charindex(@word,@s2)> 0 Set @match=@match+1
Return @match
End
--Test the function
select dbo.Matching_Words_Count('the american drug store ','the drug store inc')
returns 2
You may fix the word count to 2 or 3 and the match the tables as :
Select [Vendor Name],[Regulatory Name] From [Vendors] V LEFT OUTER JOIN [Regulatory] R ON dbo.Matching_Words_Count(v.[vendor name],R.[regulatory Name]) >=2
Hope it serves your purpose.
Regards,
Hari