0

I have recently installed Master Data Services on an instance of SQL Developer and I am now able to run the following query when the MDS database is selected in SQL Studio Manager:

SELECT mdq.Similarity(N'Alexia Geogio', N'Alexandra George', 0, 0.0, 0.0);

Now, please see the DDL below:

create table #Person (ID int not null identity, Name varchar(100), primary key (ID))
INSERT INTO #Person (Name) values ('Alexia Geogio')
INSERT INTO #Person (Name) values ('Alexandra George')
INSERT INTO #Person (Name) values ('Ian')
INSERT INTO #Person (Name) values ('Iain')

Is it possible to get an output like this with an SQL statement (three columns):

Alexia Geogio Alexandra George 0.5625
Alexandra George Alexia Geogio 0.5625
Ian Iain 0.75
Iain Ian 0.75

mdq.Similarity is a scalar valued function for anyone who did not know.

I believe that as it is not a table valued function, then there is nothing I can do.

w0051977
  • 15,099
  • 32
  • 152
  • 329
  • How are you intending to relate the rows of the table to itself? Is it always rows 1&3, 2&4, etc. or do you want to compare every row with every _other_ row (e.g. also have a result for `Alexia Geogio Ian`? – D Stanley Jan 06 '16 at 15:30

1 Answers1

2

I think a query like this does what you want:

select name1, name2, similarity
from (select p1.name as name1, p2.name as name2,
             mdq.Similarity(p1.name, p2.name, 0, 0.0, 0.0) as similarity
      from #person p1 cross join
           #person p2
     ) pp
where similarity > @SimilarityLimit  -- perhaps 0
order by similarity;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786