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»»

smart first name matching in TSQL Expand / Collapse
Author
Message
Posted Tuesday, June 18, 2013 3:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 9:22 AM
Points: 55, Visits: 559
Hello experts,

I looking for script (actually for data) for smart first name conversion.
I.e. if user enter William or Billy then script should return Bill, if user enter Alexander, Aleks, Sasha then script should return Alex, etc.
Actually I can write SQL to do it, but I can't find list of all possible first names.
Is anybody have it?

Much thanks, Alex.
Post #1464523
Posted Tuesday, June 18, 2013 8:00 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
Google "NickNames Database" and you will find a number of sources, for common English names. But there is no universal list of ALL first names. In my lifetime, I've seen such unique names as Moon Unit and Will.I.Am.

By the way, William is the proper first name. Bill, Billy, Will, and Willy are nicknames for William. This isn't to be confused with the female name "Billie"... or with people who were named simply Bill by their parents.

Having looked at this problem, it seems that we can't just arbitrarily settle on a single name. We have to generate all combinations to search on.

Good luck.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1464671
Posted Tuesday, June 18, 2013 8:07 AM


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: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Here you go...

p.s.
I got this off the internet ages ago but I can't remember where! Anyway props to the people who compiled it


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn


  Post Attachments 
NickNames.txt (23 views, 23.09 KB)
Post #1464674
Posted Tuesday, June 18, 2013 9:10 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:44 PM
Points: 133, Visits: 851
Here are an additional 2600 nicknames in my attachment to add to the attachment in the post above.

My recommendation is to separate male from female names. Also use a flattened structure; nicknames are not hierarchical. Legal name to nickname is a many-to-many relationship.

I store a soundex code with each name for indexed use.


  Post Attachments 
LegalNamePlusNickNameAdditional.txt (19 views, 38.56 KB)
Post #1464722
Posted Tuesday, June 18, 2013 5:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:58 AM
Points: 2,467, Visits: 6,437
May I humbly ask why you want to do this in the first place? My first name is Jan, which is both Dutch, Afrikaans and Polish (and probably exists in a few more languages), but it is an abbreviation of Johan/Johannes/Johannis/Ioannis, which could be Jean in French, Juan in Spanish, Ian in Irish/Scottish, and whatever else. The possible derivations will go down endlessly the further you go down the tree.

Edit: Forgot Afrikaans, sorry Host Country!


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1464919
Posted Tuesday, June 18, 2013 5:52 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 4:58 AM
Points: 2,467, Visits: 6,437
And your own name, Alex, lends itself to the same exercise (BTW, my oldest son's name is also Alex).

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1464920
Posted Wednesday, June 19, 2013 2:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 9:22 AM
Points: 55, Visits: 559
Jan Van der Eecken (6/18/2013)
May I humbly ask why you want to do this in the first place? My first name is Jan, which is both Dutch, Afrikaans and Polish (and probably exists in a few more languages), but it is an abbreviation of Johan/Johannes/Johannis/Ioannis, which could be Jean in French, Juan in Spanish, Ian in Irish/Scottish, and whatever else. The possible derivations will go down endlessly the further you go down the tree.


We do not need actually all countries / all languages abbreviation, Usa / English will be enough.
We have clients database with first name / last name, so when user try to add new client we need to check exists clients and offer to choose already created clients by search them by First / Last name. We accept that user can do mistakes and just enter i.e. Alezander instead Alexander, but if user enter Alexander and there already user with name Alex (with same Last Name) we should offer user to choose correct client from list.
Post #1464995
Posted Wednesday, June 19, 2013 2:31 AM


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: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
Jan Van der Eecken (6/18/2013)
May I humbly ask why you want to do this in the first place?


This can be useful when performing record linkage and importing data into a Single Customer View application


---------------------------------------------------------


It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Post #1464996
Posted Wednesday, June 19, 2013 4:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:51 AM
Points: 207, Visits: 840
Jan Van der Eecken (6/18/2013)
May I humbly ask why you want to do this in the first place? My first name is Jan, which is both Dutch, Afrikaans and Polish (and probably exists in a few more languages), but it is an abbreviation of Johan/Johannes/Johannis/Ioannis, which could be Jean in French, Juan in Spanish, Ian in Irish/Scottish, and whatever else. The possible derivations will go down endlessly the further you go down the tree.

Edit: Forgot Afrikaans, sorry Host Country!


I'd echo the sentiment of "why" however Ian is the English spelling - the Scottish variant is Iain - both are legal names.

The other problem the op will run up against is that one name may be the diminutive form of multiple full names. e.g. take a man commonly known as Al - is this a short form of Alan, Alain, Allan, Allen, Alun, Alfred, Alfredo, Albert, Alphonse, Alphonso, Alexander - or maybe his legal name actually is just Al.

If he is planning on using this to identify when a person may already be held in the database then fine but don't autocorrect names - it will mess up and will upset users when they are called by the wrong name.
Post #1465064
Posted Wednesday, June 19, 2013 8:16 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 4,013, Visits: 6,098
I am going to add one more wrinkle... initials. Bill Smith may actually be J. W. Smith, but the logic you are talking about would eliminate that from consideration. IMHO, if you present a pick list to choose from, you need to be careful about eliminating any possible candidates, because the end user will assume you are showing them everything. It may be safer to just select based on last name only and order by first name.



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #1465196
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse