Tip of the Day : LeetCode 586 - Customer Placing the Largest Number of Orders
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.