Capitalizing Text in SQL Server

I recently had to scramble to put together a script to change a column of strings from being all UPPERCASE to being Capitalized. It wasn’t enough to just capitalize the first letter of the string because some of the strings were multiple words that all needed to be capitalized.

Luckily I stumbled upon this function at http://www.sql-server-helper.com/functions/initcap.aspx which is an implementation of a built in function for Oracle, but is missing in MSSQL

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

I realized that I could leverage this function to aid in updating my dataset to set the strings to their proper casing. In order to accomplish this, I had to write another query which would let me update each individual row in the table with a different value.

The method I chose involved an in-memory table to store the capitalized names which I could then join to my UPDATE query to complete the process

DECLARE @Names TABLE (id INT, name NVARCHAR(100))
INSERT INTO @Names (id, name)
(SELECT [id], dbo.InitCap([name]) as name FROM Counties)

UPDATE Counties
SET name = (SELECT name FROM @Names as n WHERE n.id = Counties.id)

The temporary table has two columns, id and name. I insert the id from the base table, as well as the modified name which has the InitCap function applied to it.

Once the table is created, it’s attached to the UPDATE statement to select the correct data from the temporary table and store it to the linked column in the base table.