jbaird123
asked on
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.
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.
Scott Pletcher
I don't know of any direct "fuzzy match" operator in SQL.
ASKER
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.
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.
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
>> 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.
Yes, that is possible. Unfortunately, I don't all the details to make that work. angelIII probably does.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Both solutions will work. acperkins solution was easier to implement.