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: