Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

ProperCase Function - SQL Server

RSS
Modified on Wed, Apr 07, 2010, 1:37 PM by Administrator Categorized as Code Sample, 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2017, Patrick Jasinski.