Converting Names to Mixed Case/Camel Case in SQL Server

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

Advertisements

Comparing Uppercase and Lowercase in SQL Server

Occasionally it is necessary to really tell whether or not “THIS” is equal to “this” or “This”, including the cases.  For me, this came up when trying to assess whether a name in the database was already in mixed case or not.  If it was upper, I needed to convert it to mixed case, but if it was already in mixed case, I wanted to leave it alone.

If you just do a straight compare, case is not taken into account.  So, we need to somehow compare the actual character values of these.  It turns out that if we cast them to varbinary values, the uppercase letters do get different values than the lowercase letters that we can compare and determine the difference.

So, in my case, I decided that if the first character was in lower case or the second character was in upper case, I was probably dealing with a name that was in either all caps or all lowercase.  This is how I tested for that:

SELECT *
FROM Person
WHERE CAST(SUBSTRING(LastName, 1,1) as varbinary) = CAST(SUBSTRING(LOWER(LastName), 1,1) as varbinary)
	OR CAST(SUBSTRING(LastName, 2,1) as varbinary) = CAST(SUBSTRING(UPPER(LastName), 2,1) as varbinary)

Basically, it checks to see if the varbinary representation of the first character is equal to the varbinary representation of the uppercase value of that character OR if the varbinary representation of the second character is equal to the varbinary representation of the lowercase of that character.

This allowed me to focus in on converting only the names that were not already in mixed case/camel case.

This also works well in a function:

CREATE FUNCTION [dbo].[IsUpperCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsUpperCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(UPPER(@Char) AS VARBINARY))
		SELECT @IsUpperCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsUpperCase = CAST(0 AS BIT)

	RETURN @IsUpperCase
END

CREATE FUNCTION [dbo].[IsLowerCase]
(
	-- Add the parameters for the function here
	@Char char
)

RETURNS BIT WITH SCHEMABINDING
AS
BEGIN

	DECLARE @IsLowerCase BIT

	if (CAST(@Char AS VARBINARY) = CAST(LOWER(@Char) AS VARBINARY))
		SELECT @IsLowerCase = CAST(1 AS BIT)
	ELSE
		SELECT @IsLowerCase = CAST(0 AS BIT)

	RETURN @IsLowerCase
END

Handy ASP.NET Debug Extension Method

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

Most of the programmers I know (myself included) don’t bother with the built in Visual Studio debugging tools. They are slow and resource intensive. Usually, its more efficient to just do one or more Response.Write calls to see key data at key steps.

That can be a hassle, though. Most objects don’t print very well. You have to create a loop or write some LINQ/String.Join to write items in a collection.

Inspiration struck – couldn’t I write an extension method on object to write out a reasonable representation of pretty much anything? I could write out html tables for lists with columns for properties, etc.

Then I thought – I love the javascript debug console in firebug. I can drill down into individual items without being overwhelmed by all of the data at once. Why not have my debug information spit out javascript to write to the debug console? That also keeps it out of the way of the rest of the interface.

Here’s the code:

public static void Debug(this object value)
        {
            if (HttpContext.Current != null)
            {
                HttpContext.Current.Response.Debug(value);
            }

        }

        public static void Debug(this HttpResponse Response, params object[] args)
        {

            new HttpResponseWrapper(Response).Debug(args);
        }
        public static void Debug(this HttpResponseBase Response, params object[] args)
        {

            ((HttpResponseWrapper)Response).Debug(args);
        }
        public static void Debug(this HttpResponseWrapper Response, params object[] args)
        {

            if (Response != null && Response.ContentType == "text/html")
            {
                Response.Write("<script type='text/javascript'>");
                Response.Write("if(console&&console.debug){");

                Response.Write("console.debug(" +
                              args.SerializeToJSON() +
                               ");");
                Response.Write("}");
                Response.Write("</script>");
            }
        }

The various overloads allow:

myObject.Debug();
new {message="test",obj=myObject}.Debug();
Response.Debug("some message",myObject,myObject2);
//etc

The only other thing you’ll need is the awesome JSON.NET library for the .SerializeToJSON() call to work (which turns the .NET object into the form javascript can deal with). Get it here. FYI, the library does choke serializing some complex objects, so occasionally you’ll need to simplify before calling debug.