SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


smart first name matching in TSQL


smart first name matching in TSQL

Author
Message
onixsoft
onixsoft
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 570
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.
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12526 Visits: 6903
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? Everybody look what's going down. -- Stephen Stills
Abu Dina
Abu Dina
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2877 Visits: 3325
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
Attachments
NickNames.txt (96 views, 23.00 KB)
Bill Talada
Bill Talada
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2854 Visits: 2125
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.
Attachments
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3217 Visits: 6497
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)
Jan Van der Eecken
Jan Van der Eecken
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3217 Visits: 6497
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)
onixsoft
onixsoft
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 570
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.
Abu Dina
Abu Dina
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2877 Visits: 3325
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
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1234 Visits: 1793
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.
The Dixie Flatline
The Dixie Flatline
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12526 Visits: 6903
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? Everybody look what's going down. -- Stephen Stills
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search