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

Page History: ProperCase Function - SQL Server

Compare Page Revisions



« 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...

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


Limitations

InputResultPreferred Result
ronald mcdonaldRonald McdonaldRonald McDonald
ludwig van beethovenLudwig Van BeethovenLudwig van Beethoven
douglas-mcdonaldDouglas-McdonaldDouglas-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

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