Format names in proper case

I have been tasked on several occasions’ to present names from SQL server in the proper case when the names had been stored in all upper or lower case. Prior to SQL 2005 this could only be accomplished with lengthy and complex t-sql , but with the integration of the clr a much easier solution is available. The first means to meet the goal of proper casing our character data is to create a clr function. Regardless of your knowledge of Visual Basic or C# the amount of code and expertise necessary is truly minimal as you can see with the code provided below.

1. Start Visual Studio and create a Visual Basic Database project named
clr_fn_propercase.
2. When prompted specify the connection to the server and database that you
wish to deploy the function to.
3. From the solution explorer right click the project and choose to Add>User
Defined Function.
4. In the Name text box type in clr_fn_propercase
5. Copy and paste the below code into the window


Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class clr_fn_propercase
_
Public Shared Function clr_fn_propercase(ByVal str As SqlString) As SqlString

Return New SqlString(StrConv(str.ToString, VbStrConv.ProperCase))
End Function
End Class


This simplistic function accepts one input parameter, called str, and returns back to SQL a SqlString:
Public Shared Function clr_fn_propercase(ByVal str As SqlString) As SqlString

The entire work is done using the VbStrConv enumerator with the ProperCase member which converts our input parameter and returns the properly cased parameter back to SQL:
Return New SqlString(StrConv(str.ToString, VbStrConv.ProperCase))
You will obviously notice more code, but this is just to provide the conversion of the parameter to a string for and then back to a SqlString to be returned.

The function can then be deployed to the database directly from Visual Studio by going to the Build menu and choosing Deploy or you can use t-sql. The below statements creates the assembly in the database, this is from .dll file that is created once built in Visual Studio. In this case I moved the file to the root of c for simplicity, but the full path is required to create the assembly. The permission is set to safe, which is the default, and as this function requires no resources outside of sql, access to file, folder, registry, etc, this is sufficient

CREATE ASSEMBLY [clr_fn_propercase]
FROM 'C:\clr_fn_propercase.dll'
WITH PERMISSION_SET = SAFE


Once the assembly is created then the function is created referencing the assembly

/*Create the function from the imported assembly*/
CREATE FUNCTION clr_fn_propercase(@str NVARCHAR(100))
RETURNS NVARCHAR(100)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME clr_fn_propercase.[clr_fn_propercase.clr_fn_propercase].clr_fn_propercase

Once the assembly and function are created you can then use this function to properly case names stored in SQL.

CREATE TABLE impropercase(
data VARCHAR(100)
);
GO

INSERT impropercase
VALUES('TED GINN');
GO

INSERT impropercase
VALUES('A.J. HAWK');
GO

INSERT impropercase
VALUES('terrelle pryor');
GO

INSERT impropercase
VALUES('devier posey');
GO

INSERT impropercase
VALUES('dane sanzenbacher');
GO

SELECT dbo.clr_fn_propercase(data)
FROM impropercase;
GO

/*RESULTS
Ted Ginn
A.J. Hawk
Terrelle Pryor
Devier Posey
Dane Sanzenbacher*/

DROP TABLE impropercase;
GO

This can also be used in a report in reporting services from the Report>Report Properties>Code by adding the below:

Public Function propercase(ByVal str As String) As String
Return Strconv(str, vbpropercase)
End Function

Once the code is added it can be called in an expression:
= Code.propercase(Fields!Name.Value)