asked on

Proper Case conversion with exceptions

I am looking to create a UDF that converts a passed varchar(8000) to proper case, taking into account exceptions like MacDonald. I have a template UDF that works without any "exception handling." What I need to know is the best way to implement this "exception handling" (I place this in quotes as it is not your usual exception handling).

Two options I can think of are an array I would populate in the UDF or a temporary table I would also populate in the UDF. All of the exceptions must be contained in the UDF.

Please provide advice as to the best approach and some sample code (I haven't done a whole lot with the SQL language in terms of string manipulation). Right now I have a variable @pos indicating the current position in the string inside of a loop.

In addition, I would like to know if anyone has a decent-sized list of exceptions. Names with apostrophes where the letter capitalized is after the apostrophe have been taken care of already. Here are the exceptions I've found so far:

MacArthur
McGavley
McGrath
McGraw
McNeil
MacDonald
DeGuzman
DeJong
DellRosso
DeScenza
DiGirolamo
DiPietro
LaRose
LaRouche

ASKER CERTIFIED SOLUTION

ChrisFretwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

labreuer

ASKER

I came up with a list of one thousand names and then decided that, with the above said, that implementing special cases would be more trouble than it is worth.

If anyone still wants to come up with an answer to my first question, I'll split the points; otherwise I'll give ChrisFretwell a "B" (as he did not answer the first question).