CODEWITHSUNDEEP
sqlsql-serverfull-text-search
dub stylee
dub stylee

Reputation: 3342

JOIN two tables using full-text search in SQL Server

I have a situation where I am trying to JOIN two tables based on partially matching text data. I have read the question Using Full-Text Search in SQL Server 2005 across multiple tables, columns and it appears that my best option is to create a VIEW and add a full-text index on the VIEW.

Let me start by giving a little background of the situation. I have an Excel spreadsheet that I need to calculate some pricing for drugs, but the drug names in the spreadsheet do not match exactly to the database where I am pulling the pricing information. So I figured that using full-text search may be the way to go.

What I have done so far, is exported the spreadsheet as a CSV file and used BULK INSERT to import the data into my database. Now, my drug database has a primary key on NDC, but that information is not available on the spreadsheet unfortunately, or my job would be much easier.

I need to basically be able to match 'AMLODIPINE TAB 5MG' and 'AMLODIPINE BESYLATE 5MG TAB'. This is just one example, but the other drugs are similar. My issue is that I'm not even sure how I would be able to create a VIEW in order to add both columns, without them matching.

Is there a way to use a full-text search in a JOIN statement, something like:

SELECT i.Description, m.ProdDescAbbr
FROM dbo.ImportTable i
LEFT JOIN dbo.ManufNames m ON m.ProdDescAbbr <something similar to> i.Description

EDIT:

Not all of the drug names will contain extra words, another example that I am trying to match is: 'ACYCLOVIR TAB 800MG' AND 'ACYCLOVIR 800MG TAB'

Upvotes: 2

Views: 6831

Answers (2)

Darka
Darka

Reputation: 2768

in my work I saw this (fancy for me) function CONTAINSTABLE, which uses full text index. Maybe to much complicated function for this situation, but I wanted to share.

Returns a table of zero, one, or more rows for those columns containing precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches

Overall I see that you will need to prepare search condition (make it text) before looking for it.

Example:

SELECT select_list
FROM table AS FT_TBL 
INNER JOIN CONTAINSTABLE(table, column, contains_search_condition) AS KEY_TBL
   ON FT_TBL.unique_key_column = KEY_TBL.[KEY];

source http://msdn.microsoft.com/en-us/library/ms189760.aspx

Upvotes: 2

RyanB
RyanB

Reputation: 757

You can add a 

CREATE VIEW view_name WITH SCHEMABINDING
AS

in front of your SQL to create the view. Then you could 

CREATE UNIQUE CLUSTERED INDEX idx_name
ON view_name(Description, ProdDescAbbr)

Then you can 

CREATE FULLTEXT INDEX ON view_name

That will let you run a search with

WHERE CONTAINS( (Description, ProdDescAbbr), 'search_term')

Upvotes: 1

Related Questions