Create a SQL Server InitCap Function

Problem

You want to create a SQL Server T-SQL function similar to Oracle’s INITCAP PL/SQL function. The Oracle INITCAP function returns a string with the first letter of each word in upper-case and all the other letters in lower-case. The words are assumed to be delimited by white space or characters that are not alphanumeric. You want to achieve this goal without writing complex T-SQL code and use SQL Server’s integration with .NET in order to use the .NET rich string function support. How can this be accomplished using T-SQL?

Solution

The solution involves three major steps:

The first step is creating a static C# function residing in a .NET class as shown in Step 1 below. We will create a short C# function in a .cs file that uses the ToTitleCase C# string method that has a similar functionality to INITCAP.
The second step is to create a DLL (Dynamic Link Library) file from the .cs file as shown in Step 2. We will use the csc.exe C# compiler that exists in the .NET framework on our server. We will use the /target:library switch in order to create a DLL file.
The third step is a series of T-SQL statements all done in the SQL Server Management Studio environment as shown in Step 3. We enable the CLR integration on our server, then we create an assembly object that connects to the DLL file that was built in Step 2 and finally we create a T-SQL function that is an external wrapper for the C# method inside the assembly object. Note that the nvarchar datatype is the SQL Server matching data type to the C# string type.

When all of three steps are done, the TSQL INITCAP function can be used.

Step 1

Here is the C# code for the function. Save this to file C:\StringUtils.cs.

using System;
using System.Globalization;
public class StringUtils
    {
      public static string Initcap(string strText)
      { 
       return new CultureInfo("en").TextInfo.ToTitleCase(strText.ToLower()); 
      }
    }

Step 2

Here is how to create a DLL file using the csc.exe C# compiler utility. The location of the csc.exe file will be in the Microsoft.NET framework folder such as: C:\Windows\Microsoft.NET\Framework64\v4.0.30319.

Execute the following lines in a command shell (cmd.exe) window. You will need to find the correct path on your server for the csc.exe file. Also, see the notes at the bottom of this tip about adding this to the environment variables.

cd C:\

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /target:library StringUtils.cs

Step 3

Here is the T-SQL code. Execute the following in a SQL Server Management Studio query window.

use master
GO
-- enable CLR 
exec sp_configure 'show advanced options',1
GO
reconfigure with override
GO
exec sp_configure 'clr enabled' , 1
GO
reconfigure with override
GO
-- create assembly and function in SQL Server
CREATE ASSEMBLY StringUtils from 'c:\StringUtils.dll' WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION Initcap( @s nvarchar(2000)) 
RETURNS nvarchar(2000)
AS EXTERNAL NAME StringUtils.StringUtils.Initcap;   
GO

Example Using New InitCap Function

select dbo.Initcap ('eLI LEiba knows SQL')

The result is:

Eli Leiba Knows Sql

Further Notes

The function was tested with SQL Server 2012 and SQL Server 2014 Developer editions.
Add the path of the CSC.EXE compiler to the PATH environment variable. This will help making a .dll file from anywhere on the server.

Next Steps

Check out these related tips:

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019

Example Using New InitCap Function

Leave a ReplyCancel Reply