Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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