ProperCase Function - SQL Server

The following function will return the specified string in "proper case", using the following formatting.

Overview

Logic

  1. For words that are exactly two letters long, all letters are capitalized.
  2. For all other words, the first letter is capitalized, and all other letters are in lower case.
  3. Special handling is done for the following prefixes
    • Mc
    • Mac
    • O' (the letter "O" plus an apostrophe)
    • van ("van" followed by a space)
  4. Special handling is done for a hyphen: the first character after the first hyphen is capitalized.

Sample Output

InputResult
dorothyDorothy
washington, dcWashington, DC
mcdonaldMcDonald
macdonaldMacDonald
o'reillyO'Reilly
van beethovenvan Beethoven
mcdonald-douglasMcDonald-Douglas


SQL Code

{copytext|sql}
ALTER function [dbo].[ProperCase]
    (
    @inputString varchar(max)
    ) RETURNS varchar(max) As BEGIN

--declare @inputString varchar(max)
--set @inputString = 'Geraldine Williams       x'


    -- Declarations -------------------------------------------------------------------------------
    declare @oldlen int
    DECLARE @Result varchar(2000)
    set @oldlen = -1
    SET @Result = ''

    -- Clean up input data ------------------------------------------------------------------------
    SET @inputString = lower(ltrim(rtrim(COALESCE(@inputString, ''))))

    while @oldlen <> len(@inputstring) begin
        set @oldlen = len(@inputstring)
        set @inputstring = replace(@inputstring, '  ', ' ')
        end


    -- Loop through the String --------------------------------------------------------------------
    SET @inputString = @inputString + ' '

    WHILE 1=1 BEGIN

        -- If no more spaces, exit loop
        IF PATINDEX('% %',@inputString) = 0 BREAK

        -- Special handing for two-letter words
        IF LEN(@inputString) = 2 OR CHARINDEX(' ', @inputString) = 3 BEGIN
         
            SET @Result = @Result 
                + UPPER(LEFT(@inputString, 2))
                + SUBSTRING(@inputString, 3, CHARINDEX(' ', @inputString) - 2) 

            SET @inputString = SUBSTRING(@inputString, CHARINDEX(' ', @inputString) + 1, LEN(@inputString)) 

            END

        -- normal processing: capitalize the character after the first space ----------------------
        ELSE BEGIN

            SET @Result = @Result
                + UPPER(LEFT(@inputString, 1))
                + SUBSTRING(@inputString, 2, CHARINDEX(' ', @inputString) - 1)

            SET @inputString = SUBSTRING(@inputString, CHARINDEX(' ', @inputString) + 1, LEN(@inputString))

            END

    END

    SET @Result = substring(@Result, 1, LEN(@Result))

    -- Special Handling for Prefixes --------------------------------------------------------------
    set @Result = case 
                    when @Result like 'van %' 
                        then lower(substring(@Result,1,4)) + 
                            upper(substring(@Result,5,1)) + 
                            substring(@Result,6,len(@Result))

                    when @Result like 'Mac%' 
                        then substring(@Result,1,3) + 
                            upper(substring(@Result,4,1)) + 
                            substring(@Result,5,len(@Result))

                    when @Result like 'Mc%' 
                        then substring(@Result,1,2) + 
                            upper(substring(@Result,3,1)) + 
                            substring(@Result,4,len(@Result))

                    when @Result like 'Mc%' or @Result like 'O''%' 
                        then substring(@Result,1,2) + 
                            upper(substring(@Result,3,1)) + 
                            substring(@Result,4,len(@Result))

                    else @Result
                    end

    -- Special handing for hyphenated names -------------------------------------------------------
    set @Result = case
                    when @Result like '%-%'
                        then substring(@Result,1,charindex('-',@Result)) +
                            upper(substring(@Result,charindex('-',@Result)+1,1)) +
                            substring(@Result,charindex('-',@Result)+2,len(@Result)) 
                    else @Result
                    end

--------------------------------------------
--select Result = @Result

    RETURN @Result

END