I was recently given the task to convert names in a database to mixed case. Many of them were already in mixed case, so I wanted to leave those alone and just focus on the ones that were in all caps or all lowercase. No automated conversion could be perfect, but this got me pretty close. To view a post on determining mixed case in Sql Server, click here.
Here’s my strategy:
- If the name is already in mixed case, trust that it’s right.
- If the name is not in mixed case, then do the following:
- Trim up the name to eliminate white space
- Account for the names that start with “Mc” like “McDavid”
- Account for names with apostrophes like O’Reilly
- Account for hyphenated names (married names) “Anderson-Johnson”
- Account for multiple word names like “La Russa”
- Make sure suffixes included in the names field are capitalized appropriately
Here’s the code:
CREATE FUNCTION [dbo].[GetCamelCaseName] ( @Name varchar(50) ) RETURNS VARCHAR(50) WITH SCHEMABINDING AS BEGIN -- Capitalize the first letter and make the rest lowercase SELECT @NameCamelCase = UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 50)) -- McDavid, McReynolds, etc. --> 1st character Uppercase, 2nd Lower, 3rd Uppercase, the rest lower IF (@NameCamelCase LIKE 'mc%') SELECT @NameCamelCase = UPPER(SUBSTRING(@Name, 1, 1)) + LOWER(SUBSTRING(@Name, 2, 1)) + UPPER(SUBSTRING(@Name, 3, 1)) + LOWER(SUBSTRING(@Name, 4, 50)) -- Hyphenated Names "Anderson-White" --> Capitalize after the hyphen IF (@NameCamelCase LIKE '%-%') SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX('-', @NameCamelCase)) + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('-', @NameCamelCase) + 2, 50) -- O'Reilly, O'Neil, etc. --> 1st and 3rd upper case, the rest lower IF (@NameCamelCase LIKE '%''%') SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX('''', @NameCamelCase)) + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('''', @NameCamelCase) + 2, 50) -- I found some compound last names in parentheses in my sample --> capitalize after '(' IF (@NameCamelCase LIKE '%(%') SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX('(', @NameCamelCase) - 1) + '(' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX('(', @NameCamelCase) + 2, 50) -- Hyphenated Names "De La Hoja" --> Capitalize after each space (do it twice for names with three words) -- Change the space to '?' each time (so you actually get to the second space), then replace the '?' with ' ' IF (@NameCamelCase LIKE '% %') SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50) IF (@NameCamelCase LIKE '% %') SELECT @NameCamelCase = SUBSTRING(@NameCamelCase, 1, CHARINDEX(' ', @NameCamelCase) - 1) + '?' + UPPER(SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 1, 1)) + SUBSTRING(@NameCamelCase, CHARINDEX(' ', @NameCamelCase) + 2, 50) SELECT @NameCamelCase = REPLACE(@NameCamelCase, '?', ' ') -- Suffixes and other miscellaneous things SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' sr', ' Sr') SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' jr', ' Jr') SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' ii', ' II') SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iii', ' III') SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' DE ', ' de ') SELECT @NameCamelCase = REPLACE(@NameCamelCase, 'macdonald', 'MacDonald') if (@NameCamelCase LIKE '% iv') -- just do names that end in 'IV' so you don't change "Ivy" to "IVy" SELECT @NameCamelCase = REPLACE(@NameCamelCase, ' iv', ' IV') if ((@NameCamelCase = 'i') or (@NameCamelCase = 'ii') or (@NameCamelCase = 'iii') or (@NameCamelCase = 'iv')) SELECT @NameCamelCase = UPPER(@NameCamelCase) -- Return the result of the function RETURN ISNULL(@NameCamelCase, '') END RETURN ISNULL(@NameCamelCase, '') END
Leave a comment