Page History: ProperCase Function - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Newer Revision »

Page Revision: Wed, Aug 05, 2009, 1:36 PM

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¶

Thus 'washington, dc' returns Washington, DC. The function is not very sophisticated...

Input	Result
dorothy	Dorothy
washington, dc	Washington, DC
mcdonald	McDonald
macdonald	MacDonald
o'reilly	O'Reilly
van beethoven	van Beethoven
mcdonald-douglas	McDonald-Douglas

Limitations¶

Input	Result	Preferred Result
ronald mcdonald	Ronald Mcdonald	Ronald McDonald
ludwig van beethoven	Ludwig Van Beethoven	Ludwig van Beethoven
douglas-mcdonald	Douglas-Mcdonald	Douglas-McDonald

You have to decide for yourself whether these subtleties are important.

SQL Code¶

ALTER function [dbo].[udsProperCase]
    (
    @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