Tip of the Day : How to Call NEWID Function in a User-Defined Function
InitCap / String Capitalization Function

InitCap / String Capitalization Function

For those database developers coming from Oracle and migrating to SQL Server for whatever reason, it is sometimes frustrating to know that some of the string functions, or any functions for that matter, that you have come accustomed with in Oracle is not available in SQL Server.  One of these string functions is the InitCap function.

For those who are not familiar with this Oracle string function, InitCap is a string function that changes the first letter of a string to uppercase.  The remaining letters are made lowercase.  InitCap(<cl>) takes a single argument, where cl is a character string.  This function returns cl with the first character of each word in uppercase and all others in lowercase.

Here's a user-defined function that will simulate the behavior of the Oracle InitCap string function.

CREATE FUNCTION [dbo].[InitCap] ( @InputString varchar(4000) ) 
RETURNS VARCHAR(4000)
AS
BEGIN

DECLARE @Index          INT
DECLARE @Char           CHAR(1)
DECLARE @PrevChar       CHAR(1)
DECLARE @OutputString   VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 1

WHILE @Index <= LEN(@InputString)
BEGIN
    SET @Char     = SUBSTRING(@InputString, @Index, 1)
    SET @PrevChar = CASE WHEN @Index = 1 THEN ' '
                         ELSE SUBSTRING(@InputString, @Index - 1, 1)
                    END

    IF @PrevChar IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&', '''', '(')
    BEGIN
        IF @PrevChar != '''' OR UPPER(@Char) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index, 1, UPPER(@Char))
    END

    SET @Index = @Index + 1
END

RETURN @OutputString

END
GO

Usage

Let's say you have a table containing book titles but the data as entered in the table are all in uppercase and you want to set the titles capitalization properly.  Here's an example of your list of book titles:

SELECT [Title]
FROM [dbo].[Books]

Title
------------------------------------------------------------
A BUSINESS GUIDE TO CUSTOMER RELATIONSHIP MANAGEMENT
A PRACTICAL GUIDE TO CRM
ACCELERATING CUSTOMER RELATIONSHIPS
ACHIEVING EXCELLENCE THROUGH CUSTOMER MANAGEMENT
COMPLAINT MANAGEMENT
THE CUSTOMER MANAGEMENT SCORECARD
USING MICROSOFT CRM

Using the user-defined function above, the book titles above can look like the following:

Title
------------------------------------------------------------
A Business Guide To Customer Relationship Management
A Practical Guide To Crm
Accelerating Customer Relationships
Achieving Excellence Through Customer Management
Complaint Management
The Customer Management Scorecard
Using Microsoft Crm

To produce this result, the query is as follows:

SELECT [dbo].[InitCap] ( [Title] ) AS [Title]
FROM [dbo].[Books]

As can be seen from the output, the first character in each word is converted to uppercase while the rest of the characters of the word are all made to lowercase.  The same is the case for the word "CRM", as can be seen from the book titles "A Practical Guide To Crm" and "Using Microsoft Crm".  Instead of maintaining it to all capital letters, it was changed to "Crm".  Since the user-defined function won't be able to know which words need to be retained as all capital letters, these special cases have to be handled manually.