Proper Case User Defined Function T-SQL

The Problem

We have UPPERCASE (or lowercase) text in our database and we want to convert it to Proper Case/Title Case for reporting purposes.  This involves converting the text into lowercase and converting the first letter of each word into UPPERCASE.

e.g.

Convert this: A LONG TIME AGO IN A GALAXY FAR, FAR AWAY

To this: A Long Time Ago In A Galaxy Far, Far Away

 

Unfortunately, the problem is not always this simple; sometimes you might want to make exceptions.  In the text above you might decide that "in" and "a" are not important enough to be capitalised. 

e.g. A Long Time Ago in a Galaxy Far, Far Away

You might have other exceptions such RAM, TFT, CPU, ASCII.  Some exceptions might also be of mixed case, such as DoS, QoS, WiseSoft, TomTom. Creating a Proper Case function is not an exact science, and this is probably why it wasn't included as a system function in T-SQL. 

The Solution

A solution is required that can convert the first letter of each word into UPPERCASE and the rest of the word into lowercase.  In addition it needs to be able to handle exceptions such as those mentioned previously.  The function will allow a user to specify a list of exceptions, by inputting them into them into one of the functions parameters and also handle a few expected grammatical exceptions. 

In some situations, you might want to assume that words of a specified length should by UPPERCASE, so the function will have 3 parameters in total:

@Value  - The text you want to convert

@Exceptions – A list of exceptions.  Exceptions will be printed exactly as specified.  Specify NULL if you have no exceptions.

@UCASEWordLength – Words shorter than the value specified will automatically be printed in UPPERCASE. Specify NULL if you don’t want to use this feature. 

The function will convert the text to lower case, and then parse the text character by character.  The first character is converted to UPPERCASE. Each character is appended to a “word” variable until a white space/punctuation character is reached, marking the end of the word.  At this point the word will be compared to the list of user exceptions and the case converted if required.  Some common grammatical exceptions will also be checked – e.g. WON'T will be converted to Won't with these exceptions instead of Won'T.  The function will display names such as O'Donnell correctly.  Once the word has been cased correctly with exceptions accounted for, it is appended to a variable used to store the return value along with the white space/punctuation character following it.  The process is repeated until all of the text has been converted. 

T-SQL Code

CREATE FUNCTION [dbo].[fProperCase](@Value varchar(8000), @Exceptions varchar(8000),@UCASEWordLength tinyint)

returns varchar(8000)

as

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Function Purpose: To convert text to Proper Case.

Created By:             David Wiseman

Website:                http://www.wisesoft.co.uk

Created:                2005-10-03

Updated:                2006-06-22

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INPUTS:

 

@Value :                This is the text to be converted to Proper Case

@Exceptions:            A list of exceptions to the default Proper Case rules. e.g. |RAM|CPU|HDD|TFT|

                              Without exception list they would display as Ram, Cpu, Hdd and Tft

                              Note the use of the Pipe "|" symbol to separate exceptions.

                              (You can change the @sep variable to something else if you prefer)

@UCASEWordLength: You can specify that words less than a certain length are automatically displayed in UPPERCASE

 

USAGE1:

 

Convert text to ProperCase, without any exceptions

 

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN',null,null)

>> This Function Was Created By David Wiseman

 

USAGE2:

 

Convert text to Proper Case, with exception for WiseSoft

 

select dbo.fProperCase('THIS FUNCTION WAS CREATED BY DAVID WISEMAN @ WISESOFT','|WiseSoft|',null)

>> This Function Was Created By David Wiseman @ WiseSoft

 

USAGE3:

 

Convert text to Proper Case and default words less than 3 chars to UPPERCASE

 

select dbo.fProperCase('SIMPSON, HJ',null,3)

>> Simpson, HJ

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

begin

      declare @sep char(1) -- Seperator character for exceptions

      declare @i int -- counter

      declare @ProperCaseText varchar(5000) -- Used to build our Proper Case s