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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-09-27 : 13:41:04
|
| I've *almost* completed a SP that takes a lastname and converts it to "proper case" (Capitalizing where needed). It handles hyphenated names, "O'Donnels", multiple last names, II, III, IV, Sr, Jr, cleans up the blanks, removes special characters and so on.The next hurtle is handling Scottish names like MacGreggor - which need a Cap'ed 'G' without capitalizing the 4th character in names like Mack, Macon and so on.The solution has to come from searching a list of known 'MacNames'. (Please let me if there's a better approach) There are about 100 names to search. Other than listing all 100 in 'IF statements' , could there be a solution likeSET @found = 0SELECT @found=1 WHERE @LastName in (list..of..names)If @found = 1 -- Found a valid Scot namewhere (list..of..names) is an in-line table list in the SP so an external table in the DB is not needed to hold the names? I'm not familiar with any SQL syntax that would work.SamCFootnote - I posted a few month back to ask if ther there was a SP in the public domain that would apply proper case to a name, nothing came out of it. If anyone has a pointer to an existing SP that performs proper name case, please let me know. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-27 : 13:59:05
|
| That's not a bad approach, however it might be problematic for names such as MacNaughton; I've seen versions of it both capped and un-capped. The same applies for names beginning with "De" or "Di", I've seen some where it is supposed to be "de" or "di". Spanish and Italian names usually, and some Portugeuse, and not very common anyway, so they might not be too much of a problem.If the capitalization is strictly for your own internal consistency, then it really doesn't matter, but if you are trying to clean up data, AND make it accurately match the person's name, you probably can't fully do it automatically. However, you can still identify the ones that fall under this kind of problem relatively easily.Take a look at the Soundex and Metaphone algorithms, they detail some of these prefixes and how to recognize them:http://www.bluepoof.com/Soundex/info.htmlhttp://www.bluepoof.com/Soundex/info2.htmlhttp://www.lanw.com/java/phonetic/ |
 |
|
|
|
|
|
|
|
Proper LastName Text