MSSQL - Master Data Service; Similarity Search

Install Master Data Services from SQL setup and configure a database with this service using Master Data Service Configuration Manager.

CREATE FUNCTION [mdq].[Similarity](

@input1 [nvarchar](4000),

@input2 [nvarchar](4000), 

@method [tinyint],

@containmentBias [float],

@minScoreHint [float])

RETURNS [float] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT

AS

EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[Similarity]

CREATE FUNCTION [mdq].[SimilarityDate](@date1 [datetime], @date2 [datetime])

RETURNS [float] WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT

AS

EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[SimilarityDate]

--EXAMPLE:

SELECT DIFFERENCE(a1.ItemInstallAddress1, a2.ItemInstallAddress1),

       a1.ItemInstallAddress1, a1.ItemInstallAddress2, a1.ItemInstallZip,

       a2.ItemInstallAddress1, a2.ItemInstallAddress2, a2.ItemInstallZip

FROM Table1 a1

JOIN Table2 a2

  ON MasterDataServices.mdq.Similarity(a1.ItemInstallAddress1, a2.ItemInstallAddress1, 0, 0.85, 0)> 0.8

http://blog.hoegaerden.be/2011/02/05/finding-similar-strings-with-fuzzy-logic-functions-built-into-mds/