Creating a SQL Proper Case (propercase) user function

I’m working on a database project and had to revisit a user function that I had created a long time ago.  After evaluation the various changes and having a look at code from Tim Raster here and more from Richard McCutchen aka PsycoCoder here, I decided to combine the various features in my own code.

The code below features conversion of a string, with or without spaces and punctuation marks, to proper case.

It also understands how to spell MacIntosh, MacDonalds, et al, and features an exception list which prevents names like Maces to be written as MacEs.

The code below can be pasted straight into a SQL Code window (SQL 2008 Management Studio or and older version) to create the user defined function.

-- ==============================================
-- Author:         Roland Giesler
-- Create date:    3 May 2010
-- Description:    Change a string to proper case
-- ==============================================
 CREATE FUNCTION Proper(@value VARCHAR(8000))
 RETURNS VARCHAR(8000) AS
 BEGIN
    DECLARE @MAC_EXCEPTIONS varchar(255), @APPENDICES varchar(50), @final  VARCHAR(8000), @MyLoop int, @CharToFind varchar(1)
    -- set the Mac exeptions
    SET @MAC_EXCEPTIONS = ',Maceo,Mackinac,Mackinac  Island,Mackinaw,Mackinaw City,Macks Creek,Macks  Inn,Macom,Macomb,Macombtownship,Macon,Macy' 

    --set the initial string to lowercase and append the first separator to  be searched for
    -- this prevents a "not found" condition when searching for the first  separator
    -- and returns the whole input string unchanged when the separator is  not present in the original @value
    SET @value = LOWER(@value) + '/'
    --set the final result variable to the same as @value
    -- This is done to accomodate the loop set statements in the main loop
    SET @final = @value

    -- Repeat the whole procedure for each separator list below
    SET @MyLoop = 0
    WHILE @MyLoop < 4
       BEGIN -- Main Loop
       SET @MyLoop = @MyLoop + 1
       SET @CharToFind =
          CASE @MyLoop
             WHEN 1 THEN '/'
             WHEN 2 THEN '-'
             WHEN 3 THEN ''''
             WHEN 4 THEN ' ' -- space has to be last, to have it auto removed at ***
          END
       --set the working string to the result of the previous pass
       -- strip the appended previous separator and add the current one
       SET @value = LEFT(@final,LEN(@final)-1) + @CharToFind
       --set the final result variable to empty
       SET @final = ''

       --this ensures a loop
       WHILE 1=1
          BEGIN
          --check if "mac" appears just after a separator AND
          --the string does not appear in the MAC_EXCEPTIONS with or without  trailing punctuation
          ----------------------------------------------------------------------------------------------
          -- TO DO:
          -- NOTE: This caters for only one character like and "!" for example  trailing after the "mac" word.
          -- A beter way would be to strip all characters in a predefined  punctuation list and then lookup the
          -- word in @MAC_EXCEPTIONS.
          ----------------------------------------------------------------------------------------------
          IF PATINDEX('%mac%',@value) = 1 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-1),@MAC_EXCEPTIONS)  = 0 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-2),@MAC_EXCEPTIONS)  = 0
             SET @final = @final + 'Mac' + UPPER(SUBSTRING(@value,4,1)) +  SUBSTRING(@value,5,CHARINDEX(@CharToFind,@value)-4)

          --check if "mc" appears just after a separator AND
          --the string does not appear in the MAC_EXCEPTIONS with or without  trailing punctuation
          ELSE
             IF PATINDEX('%mc%',@value) = 1 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-1),@MAC_EXCEPTIONS)  = 0 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-2),@MAC_EXCEPTIONS)  = 0
                SET @final = @final + 'Mc' + UPPER(SUBSTRING(@value,3,1)) +  SUBSTRING(@value,4,CHARINDEX(@CharToFind,@value)-3)
             --if there are no more separators then exit the loop
             ELSE
                IF PATINDEX('%'+@CharToFind+'%',@value) = 0
                   BREAK
                ELSE
                   --now we loop through each character and uppercase letters after a  separator
                   --or at the beginning of the string
                   SET @final = @final + UPPER(LEFT(@value,1)) +  SUBSTRING(@value,2,CHARINDEX(@CharToFind,@value)-1)
          SET @value =  SUBSTRING(@value,CHARINDEX(@CharToFind,@value)+1,LEN(@value))
       END
    --
    END -- Main Loop
 -- *** The LEN function seems to ignore trailing spaces, so we don't  have to remove the last space
 SET @final = LEFT(@final,LEN(@final))
 --return the final string
 RETURN @final
 END

 --Sample Usage
 -- SELECT dbo.Proper('mAke tHIS PRopER-CASE, MacKinaW!')
Advertisements

2 thoughts on “Creating a SQL Proper Case (propercase) user function

  1. jake.snyder@gmail.com says:

    This is great. How would I adjust this function to account for PO addresses from displaying as Po?

    • You could either use a similar technique as the MAC_EXCEPTIONS I used to create an additional test for PO variances like “P.O. Box”, “PO Box”, etc. or simply use the REPLACE build-in function to replace an occurance of “Po” with “PO”. Work through the code to figure out what it does and you’ll see what I mean.

      Hope that helps enough?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s