• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 745
  • Last Modified:

SQL Server function for a fuzzy lookup

Is there a SQL Server function that can be used to execute a "fuzzy lookup"?  I am running SQL Server 2005.

The fuzzy lookup component in SSIS seems to do the trick, but I am looking for something that I can use inside a query.  

I am looking for something like this:

select *
from TABLEA, TABLEB
where TABLEA.AccountName = TABLEB.AccountName

In the example above, I want the equality sign to be replaced with some sort of a fuzzy match.
0
jbaird123
Asked:
jbaird123
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Scott PletcherSenior DBACommented:
I don't know of any direct "fuzzy match" operator in SQL.
0
 
jbaird123Author Commented:
ScottPletcher: This does not have to be a "direct" operator.  I would be willing to settle for creating a custom function that performed the operation.  
0
 
Scott PletcherSenior DBACommented:
Rather than try to reproduce the way the SSIS fuzzy function works, you might just want to invoke a pkg to do the fuzzy matching for you.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sql server has full-text indexing, which works with "fuzz" logic, but does not work for "join" in the way you "need".

so, what does the "fuzzy" logic need to "match"? can you provide examples of what should "match" and what not?
0
 
jbaird123Author Commented:
ScottPletcher:

Your suggestion may work, but I don't understand how to implement it.  For example, whenever I execute an SSIS Package, I use SQL Agent to run the package on a schedule.  I am looking for a way to execute this package on demand.  For example, an end user may want to open a web screen, retrieve a list of accounts, and then execute a process to find possible matches and display them on the screen.  Is it possible to have an action on the web screen trigger the SSIS package?  If it is possible to have some user action (like clicking a button) trigger an SSIS package to execute, then this will solve my problem.

Thanks.
0
 
jbaird123Author Commented:
angelIII:  

I have company names in one database that need to be cross-referenced against company names in another database.  Because the company names will not be identical in both systems, I need to be able to find "possible or likely" matches.

Example:  "John's Heating and Cooling"  may need to be matched with "Johns Heating, Cooling".  

I want to build a web screen which will display one account with a list of possible or likely matches underneath.  This will allow a user to select the proper match and build the cross reference table.  The primary purpose is to make it easy for users to find close matches.
0
 
Scott PletcherSenior DBACommented:
>> If it is possible to have some user action (like clicking a button) trigger an SSIS package to execute, then this will solve my problem. <<

Yes, that is possible.  Unfortunately, I don't all the details to make that work.  angelIII probably does.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, I would say you should check out the full text index:
http://msdn.microsoft.com/en-us/library/ms142559.aspx
0
 
Anthony PerkinsCommented:
This is going to be pretty ugly, but you could try something like this:
SELECT  *
FROM    TABLEA a
        INNER JOIN TABLEB b ON SOUNDEX(a.AccountName) = SOUNDEX(b.AccountName)

Open in new window

0
 
Anthony PerkinsCommented:
For an even "fuzzier" match you can do something like this:
SELECT  *
FROM    TABLEA a,
        TABLEB b
WHERE   DIFFERENCE(a.AccountName, b.AccountName) = 3

Open in new window

0
 
jbaird123Author Commented:
Thank you.  Both solutions will work.  acperkins solution was easier to implement.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now