Create SQL Server CLR User-Defined Scalar Functions ‘Titleize’

image source: https://i.ytimg.com/vi/f_nVh0U8zRI/maxresdefault.jpg

In Microsoft own words: User-defined functions are routines that can take parameters, perform calculations or other actions, and return a result. Beginning with SQL Server 2005 (9.x), you can write user-defined functions in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#.

There are two types of functions: scalar, which returns a single value, and table-valued, which returns a set of rows.

we’ll be creating a user-defined scalar function to Titleize that gets a string like “I’M NO JEDI” and return “I’m No Jedi” with the first letter and first letter following a space in upper case.

Create a new Visual Studio project

Create a new C# Class Library (.NET Framework)

Create a new project

Setup Project

Set a Project name

Add Scalar Function

Rename the Class.cs name to CLRFunctions.cs and add the code below

Build the project and upload the DLL file to the server.

The execution result

Execution Result