Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Match firstname lastname Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2003 5:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 8:49 AM
Points: 13, Visits: 4
Hi,

Could anyone help me with T-SQL to match names. I have to match names from one database to bank account details entered by clients in another. The problem is that clients enter full name, initials, fistname last and lastname first, spouses names etc. for account details. Huge dilemma!

Thanks in advance,

diesel





Post #14416
Posted Tuesday, July 22, 2003 10:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 6, 2004 3:29 AM
Points: 120, Visits: 1
Instead of trying to compare single column values to multivalue columns, look at using the like operator to search through the multi-value columns looking for instances of the name you are searching. If you post your schema, we can show you the T-SQL.




Post #71569
Posted Tuesday, July 22, 2003 12:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2003 12:00 AM
Points: 34, Visits: 1
I agree that seeing the schema will up. The logic will might be a bit complex, may have to have more than 1 temp table, but again without the schema (from both databases), it's a hard call.




Post #71570
Posted Tuesday, July 22, 2003 3:14 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, May 30, 2007 4:59 PM
Points: 672, Visits: 1
Maybe more of the business rules aswell, how do you determine if Joe Doe is really Joe Doe in the other database, both maybe married to a Sue.


Post #71571
Posted Wednesday, July 23, 2003 2:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:45 PM
Points: 2,892, Visits: 1,784
How are you going to cater for Rob, Bob, Robert, Robin, Bert. They could all be the same person or they could be all different.

Speaking as someone who has done more name and address matching than I've had hot dinners this is not something you should do on financially sensitive data such as bank accounts.

The best you could hope for would be and exact name and address match, but that is likely to be about 40% of cases if you are lucky.



LinkedIn Profile
Newbie on www.simple-talk.com
Post #71572
Posted Wednesday, July 23, 2003 5:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 28, 2009 8:49 AM
Points: 13, Visits: 4
All valid points. I have done a link to the QAS package we use, (ACE in the USA) which narrows it down. I guess that considering it's not the brightest of requests I have had from Customer Services I will send the extract to them to wade through. Revenge is sweet.
Thanks for the help!




Post #71573
Posted Monday, September 24, 2012 7:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 7:48 AM
Points: 3, Visits: 53
I would use Fuzzy Matching in SSIS. Throw everything to temp table. Remove your direct matches and then fuzzy matching the rest. Sorry can't give more details, but I did this in a project once when we were trying to correlate various school lists across government agencies. A joy that was.
Post #1363471
Posted Monday, September 24, 2012 7:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
I don't think the OP will notice because he hasn't logged in since 2009....
Post #1363479
Posted Monday, September 24, 2012 8:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 7:48 AM
Points: 3, Visits: 53
True, but others reading through a forum might be looking for ideas on how to tackle problems like this.
Post #1363553
Posted Monday, September 24, 2012 2:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:52 PM
Points: 36,773, Visits: 31,229
emoore 99634 (9/24/2012)
True, but others reading through a forum might be looking for ideas on how to tackle problems like this.


+1


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1363717
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse