A simple Formatted ToolTip text on hover

The task was to add a formatted “ToolTip” message when hovering on a “More” link.  On a certain report, some of the names got pretty long.  Instead of wrapping text on the report, the desire was to simply truncate the name, then allow the user to see the entire name by hovering over the “More” link.  However, we wanted more than just the built-in ToolTip look.

I’ve seen JavaScript implementations of this, but I was looking for a simpler solution.  This is what I came up with:

The Mark-up

<td>
    <%# (Eval("Name").ToString().Length > 65) ? Eval("Name").ToString().Substring(0, 60) + "..." : Eval("Name")%>
    <span>
        <a href="#"><%# (Eval("Name").ToString().Length > 65) ? "More" : "" %></a>
        <span><%#Eval("Name")%></span>
    </span>
</td>

The CSS

.showonhover .hovertext { display: none;}
.showonhover:hover .hovertext {display: inline;}
a.viewdescription {color:#999;}
a.viewdescription:hover {background-color:#999; color: White;}
.hovertext {position:absolute;z-index:1000;border:1px solid #ffd971;background-color:#fffdce;padding:11px;width:150px;font-size: 0.75em;}

Comment

The key is the first two lines of the CSS, specifically hiding the .hovertext node until the mouse hovers over the .showonhover node.  This is a pretty simple solution, but it wasn’t immediately apparent to me, so I thought I’d share it with others who may have the same initial mental block that I had.

IEnumerable.Flatten in C#

One thing I’ve seen fairly often is this:

public IEnumerable<Relationship> GetRelationships()
{
     var itemRels = (from i in DataContext.Items
                      select i.Relationships);

     var relList = new List<Relationship>();
     foreach (var rels in itemRels) {
          relList = relList.Concat(rels).ToList();
     }

     return relList;
}


This is essentially the same as the “Flatten” function that exists in many other languages, i.e.
var array = [1,2][3][4,5,6].flatten() // array = [1,2,3,4,5,6];
There is a built in extension for selecting objects like this in Linq :
     return Datacontext.Items.SelectMany(i => i.Relationships);


		
	

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

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