SQLServerPedia

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)

For those lucky enough to be attending the SQL PASS summit this year in Seattle, WA I’ll be there to.  This is my third year in a row attending, and my first year speaking.  Over the course of the week I’ve got a bunch of planned things which ...

Sankar Reddy (Blog – Twitter) put a ton of work into SQLServerPedia over the last couple of months, banging out some great wiki articles:

• Getting the Last Clean DBCC CHECKDB Date
• Tracking Password Changes
• Server Audit Examples

A group of SQL Server DBAs and bloggers voted the DBCC CHECKDB date article ...

Flying out on Monday, and I’m so excited.  Here’s the stuff I’m throwing in my bag (as if any of you care). My Archos 5 IMT – 6+ hours and an extended battery.  Gotta load it up with all of the TV shows I’ve missed over the last few months on my SageTV rig.  Since I [...]

The Professional Association for SQL Server (PASS) is an independent, not-for-profit association, dedicated to supporting, educating, and promoting the Microsoft SQL Server community. From local user groups and special interest groups (Virtual Chapters) to webcasts and the annual PASS Community Summit – the largest gathering of SQL Server professionals in the world – PASS is [...]

I discovered this little catch back when SQL 2005 released, but it took me awhile to notice it was not working.  One of the cool new things in the 2005 release was database mail.  Finally we didn’t have to setup mail profiles and install Outlook, which meant additional patching ...

OK folks, sorry about having to cancel practice last week but we didn’t want to take any chances with the current swine flu pandemic that is sure to be made into a TV movie to be shown during May sweeps next year. I know that many of you had ...

Feeling the crunch today to finish up and hand off work before leavign for #SQLPASS (yeah, I used a Twitter hashtag in a blog...wanna make something of it? :)  so we're going with the "clean, efficient" style today.  Free stuff for you:

• Thanks @BuckWoody for the list of free ...

Allen Kinsel on Twitter (@sqlinsaneo) recently started a new Twitter tag, #PASSAwesomeness, about all of the cool things about PASS Summit. I really like the tag, so I’m going to blatantly steal borrow it for this post. First, and long overdue, I want to give a brief recap of the East Iowa [...]