Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scarmody
Starting Member
2 Posts |
 Posted - 2010-10-22 : 13:26:09
|
| For our business we have a list of over 1 million customers in our Customers table that have participated in a transaction. Customer names are supplied to us by the Credit Card company we work with in one field (no control over this). Say someone named Tom Smith has several credit cards and uses them in transactions with us. Each card could have a different name (i.e. 'Tom Smith', 'T. Smith', 'Smith, Thomas', 'Thomas A. Smith', etc). Each of these would be a different row in our table associated to a different card. In an effort to monitor fraudulent transactions one of the departments here has provided a list of names (around 1000) they wish to monitor. On the list is 'Tom Smith' for example.If I join this list (imported into a SQL table) on customer name I will match on one of the rows in the Customer table. However I would like to pick up on all of the variations I described above. I have tried various things but either they are limiting or the queries take too long and I have to stop them.Would fuzzy logic in SSIS or some other way help? I have tried a first cut at that but it took a very long time to run. Full-Text indexing perhaps? - I don't have any experience with setting that up. The ultimate goal would be a SSRS report of transactions involving the names on the list provided (and hopefully their variations which were not provided but which we potentially have in our Customer table).Any help would be appreciated. Scott |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-10-22 : 14:24:04
|
| I would go for Full Index search.You have to enable full index search on your table.Refer BOL for more detailsPBUH |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
scarmody
Starting Member
2 Posts |
Posted - 2010-10-25 : 09:20:29
|
| Looks interesting..but how would it handle nicknames (i.e 'Parker, Tony' and 'Anthony Parker'). Would I need to join to a table of nicknames to get all possible combinations? If so does anyone know where a comprehensive list would be? |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-10-25 : 18:59:50
|
| Look at Melissa Data tools for just this sort of thing. Don't try to write it yourself; it is really complicated.--CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
Matching on a list of names